Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a stored procedure with a single SELECT statement inside:

DROP PROCEDURE `pos_neg_test`//
CREATE PROCEDURE `pos_neg_test`(IN start_ts TIMESTAMP, IN end_ts TIMESTAMP)
    BEGIN
        SELECT `title_id`, `total`, `pos`, `neg` FROM 
        (SELECT 
            COUNT(message_id) AS total , 
            title_id 
        FROM `messages_has_titles` 
        INNER JOIN `messages` USING(message_id) 
        WHERE msg_time < FROM_UNIXTIME(end_ts) AND msg_time >= FROM_UNIXTIME(start_ts) 
        GROUP BY title_id
        ) total  
    LEFT OUTER JOIN  
        (SELECT 
            COUNT(message_id) AS pos , 
            title_id FROM `messages_has_titles` 
        INNER JOIN `messages` USING(message_id) 
        WHERE msg_time < FROM_UNIXTIME(end_ts) AND msg_time >= FROM_UNIXTIME(start_ts) AND sentiment > 0     
        GROUP BY title_id
        ) pos 
    USING (title_id)  
    LEFT OUTER JOIN 
        (SELECT 
            COUNT(message_id) AS neg , 
            title_id FROM `messages_has_titles` 
        INNER JOIN `messages` USING(message_id) 
        WHERE msg_time < FROM_UNIXTIME(end_ts) AND msg_time >= FROM_UNIXTIME(start_ts) AND sentiment < 0 
        GROUP BY title_id
        ) neg 
    USING (title_id);
END;

When I call this procedure from the mysql CLI, I got an empty result:

mysql> CALL pos_neg_test(1348142600, 1348143200);
Empty set (0.24 sec)

Query OK, 0 rows affected, 2 warnings (0.24 sec)

SHOW WARNINGS tells me, that my input argument got truncated:

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1265 | Data truncated for column 'start_ts' at row 1 |
| Warning | 1265 | Data truncated for column 'end_ts' at row 1   |
+---------+------+-----------------------------------------------+
0 rows in set (0.00 sec)

What did I do wrong?

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

MySQL timestamp is not a Unix timestamp. It has the form '2012-02-02 12:12:12'. I think it is worth to mention that it differs from datetime in that while timestamps minimum and maximum values correspond to that of a Unix timestamp, datetime has a broader range.

See the docs here: http://dev.mysql.com/doc/refman/5.5/en/datetime.html

The easiest solution is changing arguments to integers. The other way is of course producing valid timestamps in the calling application and feed those to your stored proc, but in this case you will have to modify the proc as well (by removing the FROM_UNIXTIME conversions). (Thanks for DTest for the suggestion.)

share|improve this answer
1  
+1 exactly this, recommend adding to the answer to change params to INT if OP wants to pass in the unix timestamp –  Derek Downey Sep 20 '12 at 13:42
    
Thank you very much! –  Thomas Sep 20 '12 at 13:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.