mysql time类型 查询_MYSQL的time类型值进行统计

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

0

顶

1

踩

分享到：

2010-06-01 14:14

浏览 1479

分类:数据库

评论