sql执行报错Truncated incorrect time value的解决方法

一、前言

最近修改生产库，需要更新一批数据。

1.首先，执行了这个sql：

select count(*) from class_subject 
where endtime is not null and starttime is not null and round(TIME_TO_SEC(TIMEDIFF(endtime,starttime))/60/60,1)!=round(hour,1)
1
2

就是查询下结束时间-开始时间!=hour的数据，这个执行没有问题

2.然后，执行这个sql，就报错了：

update class_subject set hour=round(TIME_TO_SEC(TIMEDIFF(endtime,starttime))/60/60,1)
where endtime is not null and starttime is not null and round(TIME_TO_SEC(TIMEDIFF(endtime,starttime))/60/60,1)!=round(hour,1)
1
2

就是想设置下hour=结束时间-开始时间，但是报错：

SQL错误[1292][22001]:Data truncation:Truncated incorrect time value:
'2616:30:00'
1
2

二、排查过程

百度发现，有的说：

1.根本原因是这个函数是算两个日期的时间差，但是他的取值最大只到838:59:59(不到35天)，如果超过就会报错

但是我们排查了下数据库数据，round(TIME_TO_SEC(TIMEDIFF(endtime,starttime))/60/60,1)的结果最多只差3小时，不存在超过35天的情况。

三、解决方法

首先我们的目标是更新数据库的一批数据，其次发现执行select是没有问题的，因此我们决定查询出所有数据的id与待更新的值来，一条一条批量更新。

1.使用了sql:

select id, round(TIME_TO_SEC(TIMEDIFF(endtime,starttime))/60/60,1)!=round(hour,1) as hour from class_subject 
where endtime is not null and starttime is not null and round(TIME_TO_SEC(TIMEDIFF(endtime,starttime))/60/60,1)!=round(hour,1)
1
2

这样可以查询到待修改数据的id，以及需要修改的目标hour值

2.使用excel，把数据批量拼接成以下格式：

update class_subject set hour='xxx' where id='xxx';
update class_subject set hour='xxx' where id='xxx';
update class_subject set hour='xxx' where id='xxx';
1
2
3

3.然后，一条一条批量更新，就绕过了这个报错，更新数据成功。