Mysql – Stored procedure arguments get immediately truncated

MySQLstored-procedures

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?

Best Answer

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.)

Related Question