MYSQL的time类型值进行统计

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)格式，须加上此函数