Mysql-Truncated incorrect time value: '1000:59:44'

前言：今天编写一个存储过程，条件是创建时间与当前时间相比，如果大于24小时，更新状态为失效。使用了timediff函数，出现了这个错误。记录如下；

 

update app_order_pay_url_info set url_status=2  where url_status=1 and HOUR(TIMEDIFF(NOW(),create_time))>24;

报如下的错误。

Truncated incorrect time value: '1000:59:44'

 

测试了一下，原来问题在这里：

select TIMEDIFF(NOW(),'2013-10-02 22:22:22') a,HOUR(TIMEDIFF(NOW(),'2013-10-02 22:22:22')) b;
a                                                           b 

838:59:59                                           838

--于是改用了这个函数，来测试

select UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP('2013-11-12 22:22:22')；

update app_order_pay_url_info set url_status=2  where url_status=1
and UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(create_time)>86400;

 

Ok，这回成功执行。

 

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

 

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