错误代码:1292.截断了不正确的时间值 [英] Error Code: 1292. Truncated incorrect time value

问题描述

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

这给了我 2 个结果

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

这给了我:错误代码:1292.截断了不正确的时间值"

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

SESSION_CREATED 是 TIMESTAMP 数据类型

SESSION_CREATED is TIMESTAMP Datatype

实际数据:

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?

推荐答案

为什么要用这么复杂的表达式?为什么不这样做:

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

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

至于为什么你的代码可能会失败，它使用了timediff()，它被限制在时间数据类型的范围内.这是:

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 以HH:MM:SS"格式(或'HHH:MM:SS' 格式用于大小时值).TIME 值的范围可以从-838:59:59"到838:59:59".

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

因为您使用的是 NOW()，所以值会从一次迭代更改为下一次迭代.您刚好在数据不太旧时运行 SELECT ，然后在数据太旧时运行 DELETE .

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.

这篇关于错误代码:1292.截断了不正确的时间值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！