Question :
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?
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.)