Mysql Truncated incorrect time value

Mysql Truncated incorrect time value

发现一个mysql的问题,当我们做数据定时更新操作的时候，比如说定时作废48小时内未付款的订单的时候，如果用到mysql TIMEDIFF(var1,var2)函数

例如：UPDATE justice_user_customer SET STATUS=0 WHERE TIMEDIFF(SYSDATE(),update_time)>='48:00:00'

这个sql一开始没有问题，完全正常，但是某一天会突然报错，会出现Truncated incorrect time value: '1000:59:44' 这样奇怪的错误，根本原因是这个函数是算两个日期的时间差，但是他的取值最大只到838:59:59(不到35天)

所以会出现这样一个现象，一开始执行成功的sql，到了某一天不知不觉的报错了！

为了防止出现这样的错误，改用如下sql

 

UPDATE justice_user_customer SET STATUS=0 WHERE UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(update_time)>172800（2天）

 

--摘录官方文档函数的解释

 

timediff(expr1,expr2)

timediff() returns expr1 – expr2 expressed as a time value. expr1 and expr2 are time or date-and-time expressions, but both must be of the same type. 

 

unix_timestamp(), unix_timestamp(date)

if called with no argument, returns a unix timestamp (seconds since '1970-01-01 00:00:00' utc) as an unsigned integer. if unix_timestamp() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' utc. date may be a date string, a datetime string, a timestamp, or a number in the format yymmdd or yyyymmdd. the server interprets date as a value in the current time zone and converts it to an internal value in utc. clients can set their time zone as described in section 10.6, “Mysql server time zone support”.