Error Code: 1292. Truncated incorrect time value

Question

SELECT *
FROM SESSIONS
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), SESSION_CREATED)) / 3600 >= 24

This give me 2 results

DELETE FROM SESSIONS
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), SESSION_CREATED)) / 3600 >= 24

And this give me: "Error Code: 1292. Truncated incorrect time value"

SESSION_CREATED is TIMESTAMP Datatype

Actual data:

SESSION_ID      SESSION_CREATED
223133          2017-05-22 07:14:34
223134          2017-05-22 07:14:36

How can the select work but not the delete?

Answer 1

Why are you using such a complicated expression? Why not just do:

DELETE FROM SESSIONS
    WHERE SESSION_CREATED < NOW() - INTERVAL 1 DAY;

As for why your code might fail, it is using timediff() which is limited to the range of the time data type. And this is:

MySQL retrieves and displays TIME values in 'HH:MM:SS' format (or 'HHH:MM:SS' format for large hours values). TIME values may range from '-838:59:59' to '838:59:59'.

Because you are using NOW(), the values change from one iteration to the next. You just happened to run the SELECT when the data wasn't too old and then the DELETE when it was.

Answer 2

Example for Timediff using TIMESTAMPDIFF on MySQL:

To use TIMESTAMPDIFF, define the unit (SECOND, MINUTE, HOUR...), the initial and end date (must be timestamp's datatype).

ROUND( TIMESTAMPDIFF(HOUR, initial_date, CURRENT_TIMESTAMP()) / 24, 2 )