MYSQL的time类型值进行统计

痴心VS绝对

1.实现功能场景：统计一天中工作时间
2.建表：create table `loadmovement` (
`uuid` varchar (96),
`criminalId` varchar (96),
`movementdate` datetime ,
`exitTime` varchar (60),
`ReturnTime` varchar (60),
`nextDateIndicator` double ,
`moveDays` double ,
`modifyTime` datetime ,
`createTime` datetime ,
`valid` double
);
insert into `loadmovement` (`uuid`, `criminalId`, `movementdate`, `exitTime`, `ReturnTime`, `nextDateIndicator`, `moveDays`, `modifyTime`, `createTime`, `valid`) values('402880e929c616bf0129c6178c3a0003','402880e928c7ca190128c7dd863d0005','2010-07-12 17:58:38','11:15:00','00:30:00','1','2','2010-07-12 19:05:44','2010-07-12 17:58:38','0');
insert into `loadmovement` (`uuid`, `criminalId`, `movementdate`, `exitTime`, `ReturnTime`, `nextDateIndicator`, `moveDays`, `modifyTime`, `createTime`, `valid`) values('402880e929c97a710129c9870e280003','402880e928c7ca190128c7dd863d0005','2010-07-13 09:59:18','01:00:00','00:30:00','1','2','2010-07-13 10:14:02','2010-07-13 09:59:18','0');
insert into `loadmovement` (`uuid`, `criminalId`, `movementdate`, `exitTime`, `ReturnTime`, `nextDateIndicator`, `moveDays`, `modifyTime`, `createTime`, `valid`) values('402880e929c97a710129c9870e280004','402880e928c7ca190128c7dd863d0005','2010-07-13 09:59:18','11:30:00','01:00:00','1','2','2010-07-13 10:04:12','2010-07-13 09:59:18','0');
insert into `loadmovement` (`uuid`, `criminalId`, `movementdate`, `exitTime`, `ReturnTime`, `nextDateIndicator`, `moveDays`, `modifyTime`, `createTime`, `valid`) values('402880e929c97a710129c9870e280005','402880e928c7ca190128c7dd863d0005','2010-07-13 09:59:18','11:00:00','00:00:00','1','1','2010-07-13 10:12:30','2010-07-13 09:59:18','0');
insert into `loadmovement` (`uuid`, `criminalId`, `movementdate`, `exitTime`, `ReturnTime`, `nextDateIndicator`, `moveDays`, `modifyTime`, `createTime`, `valid`) values('402880e929c97a710129c9870e280006','402880e928c7ca190128c7dd863d0005','2010-07-13 09:59:18','11:45:00','01:00:00','1','3','2010-07-13 10:09:27','2010-07-13 09:59:18','0');
insert into `loadmovement` (`uuid`, `criminalId`, `movementdate`, `exitTime`, `ReturnTime`, `nextDateIndicator`, `moveDays`, `modifyTime`, `createTime`, `valid`) values('402880e929c97a710129c99b1b56000d','402880e928c7ca190128c7dd863d0005','2010-07-13 10:21:12','11:30:00','00:45:00','1','2','2010-07-13 10:22:08','2010-07-13 10:21:12','0');
insert into `loadmovement` (`uuid`, `criminalId`, `movementdate`, `exitTime`, `ReturnTime`, `nextDateIndicator`, `moveDays`, `modifyTime`, `createTime`, `valid`) values('402880e929c97a710129c9a42378000f','402880e928c7ca190128c7dd863d0005','2010-07-13 10:31:04','11:30:00','00:15:00','1','4','2010-07-13 10:31:04','2010-07-13 10:31:04','1');
insert into `loadmovement` (`uuid`, `criminalId`, `movementdate`, `exitTime`, `ReturnTime`, `nextDateIndicator`, `moveDays`, `modifyTime`, `createTime`, `valid`) values('402880e929c97a710129c9a423780010','402880e928c7ca190128c7dd863d0005','2010-07-13 10:31:04','11:30:00','01:00:00','1','2','2010-07-13 10:31:04','2010-07-13 10:31:04','1');
insert into `loadmovement` (`uuid`, `criminalId`, `movementdate`, `exitTime`, `ReturnTime`, `nextDateIndicator`, `moveDays`, `modifyTime`, `createTime`, `valid`) values('402880e929c97a710129c9a423780011','402880e928c7ca190128c7dd863d0005','2010-07-13 10:31:04','11:00:00','00:45:00','1','1','2010-07-13 10:31:04','2010-07-13 10:31:04','1');

3.查询：select moveDays,  TIME_FORMAT(
SEC_TO_TIME(sum(case when nextDateIndicator=1
then
TIME_TO_SEC(returnTime)-TIME_TO_SEC(exitTime)+TIME_TO_SEC('24:00:00')
else
TIME_TO_SEC(returnTime)-TIME_TO_SEC(exitTime)
end)),'%T') as calcTime, returnTime,exitTime
from loadmovement where criminalId='402880e928c7ca190128c7dd863d0005'
group by moveDays
4.说明TIME_FORMAT非常重要，本人就是缺少了这个函数，致使在前端的显示为(1970/1/1),若要显示为(HH:mm:ss)格式，须加上此函数