我这边的mysql数据库格式内容是
utc
2018-05-14 20:05:00
2018-05-14 20:07:30
2018-05-14 20:08:30
2018-05-14 20:09:30
2018-05-14 20:13:00
2018-05-14 20:16:00
2018-05-14 20:28:00
2018-05-14 20:30:30
2018-05-14 20:33:30
2018-05-14 20:41:30
2018-05-14 20:43:30
我要的计算方式是,如果把以上记录的时间汇总成一共运行的分钟, 不能用最后的时间-开始时间,因为中间日期可能会有不连贯的情况。
utc
2018-05-14 20:05:00
2018-05-14 20:07:30
2018-05-14 20:08:30
2018-05-14 20:09:30
2018-05-14 20:13:00
2018-05-14 20:16:00
2018-05-14 20:28:00
2018-05-14 20:30:30
2018-05-14 20:33:30
2018-05-14 20:41:30
2018-05-14 20:43:30
我要的计算方式是,如果把以上记录的时间汇总成一共运行的分钟, 不能用最后的时间-开始时间,因为中间日期可能会有不连贯的情况。
create table ldf(
id int AUTO_INCREMENT,
val datetime,
primary key(id)
);
insert into ldf(val)
values
('2018-05-14 20:05:00'),
('2018-05-14 20:07:30'),
('2018-05-14 20:08:30'),
('2018-05-14 20:09:30'),
('2018-05-14 20:13:00'),
('2018-05-14 20:16:00'),
('2018-05-14 20:28:00'),
('2018-05-14 20:30:30'),
('2018-05-14 20:33:30'),
('2018-05-14 20:41:30'),
('2018-05-14 20:43:30');select a.val,b.val,unix_timestamp(a.val)-unix_timestamp(b.val) from
(select @n:=@n+1 as row,val from (select @n:=0) r,ldf) a,
(select @n1:=@n1+1 as row,val from (select @n1:=1) r,ldf) b
where a.row=b.rowselect sum(unix_timestamp(a.val)-unix_timestamp(b.val)) as total_s from
(select @n:=@n+1 as row,val from (select @n:=0) r,ldf) a,
(select @n1:=@n1+1 as row,val from (select @n1:=1) r,ldf) b
where a.row=b.row