ze, MySQL - rextester

ze

drop table a8;
create table a8(id integer,start datetime,ende datetime);
insert into a8(id, start, ende) values (1, '2018-9-15T10:00','2018-9-15T20:44');
insert into a8(id, start, ende) values (1, '2018-9-25T10:00','2018-9-25T20:44');
insert into a8(id, start, ende) values (1, '2018-5-16T10:00','2018-5-16T20:44');
insert into a8(id, start, ende) values (1, '2018-6-16T10:00','2018-6-16T11:01');
insert into a8(id, start, ende) values (1, '2018-6-17T10:00','2018-6-17T11:01');
insert into a8(id, start, ende) values (1, '2018-7-10T16:00','2018-7-10T17:59');
insert into a8(id, start, ende) values (1, '2018-7-17T16:00','2018-7-17T17:59');
insert into a8(id, start, ende) values (1, '2018-7-18T12:30','2018-7-18T17:59');
insert into a8(id, start, ende) values (1, '2018-8-17T16:00','2018-8-17T17:59');

insert into a8(id, start, ende) values (1, '2017-7-10T16:00','2017-7-10T17:59');
insert into a8(id, start, ende) values (1, '2017-7-17T16:00','2017-7-17T17:59');
insert into a8(id, start, ende) values (1, '2017-7-18T12:30','2017-7-18T17:59');
insert into a8(id, start, ende) values (1, '2017-8-17T16:00','2017-8-17T17:59');
insert into a8(id, start, ende) values (1, '2017-1-17T16:00','2017-1-17T17:59');

select 
sum(time_to_sec(timediff(ende,start))) Sekunden,
year(start) Jahr,
month(start) Monat,
truncate(sum(time_to_sec(timediff(ende,start)))/3600,0) Stunden,
truncate(
    (sum(time_to_sec(timediff(ende,start))) - truncate(sum(time_to_sec(timediff(ende,start)))/3600,0) * 3600)
/60,0) Minuten
from a8
group by year(start),month(start)
order by year(start) desc,month(start) desc


run \| edit \| history \| help	0