Potrzebujesz tabeli/ widoku w której będziesz miał liczby od 0 do 23, w przykładzie tabela ol
z kolumną n
, i teraz tabela w której masz dane dt
, musisz zrobić joina, żeby kolumna n była pomiedzy godziną poczatku i końca, to "rozbije" poszczegolne godziny na tyle rekordow ile to trwa godzin, reszta to policzenie róznicy w minutach i zgrupowanie danych:
SELECT
sum(case when n = 0 then d else 0 end) `0`
,sum(case when n = 1 then d else 0 end) `1`
,sum(case when n = 2 then d else 0 end) `2`
,sum(case when n = 3 then d else 0 end) `3`
,sum(case when n = 4 then d else 0 end) `4`
,sum(case when n = 5 then d else 0 end) `5`
,sum(case when n = 6 then d else 0 end) `6`
,sum(case when n = 7 then d else 0 end) `7`
,sum(case when n = 8 then d else 0 end) `8`
,sum(case when n = 9 then d else 0 end) `9`
,sum(case when n = 10 then d else 0 end) `10`
,sum(case when n = 11 then d else 0 end) `11`
,sum(case when n = 12 then d else 0 end) `12`
,sum(case when n = 13 then d else 0 end) `13`
,sum(case when n = 14 then d else 0 end) `14`
,sum(case when n = 15 then d else 0 end) `15`
,sum(case when n = 16 then d else 0 end) `16`
,sum(case when n = 17 then d else 0 end) `17`
,sum(case when n = 18 then d else 0 end) `18`
,sum(case when n = 19 then d else 0 end) `19`
,sum(case when n = 20 then d else 0 end) `20`
,sum(case when n = 21 then d else 0 end) `21`
,sum(case when n = 22 then d else 0 end) `22`
,sum(case when n = 23 then d else 0 end) `23`
FROM (
select
n
,(time_to_sec(case when n=eh then endtime else STR_TO_DATE(concat_ws(':',n+1,0,0),'%H:%i:%s') end)
-time_to_sec(case when n=sh then starttime else STR_TO_DATE(concat_ws(':',n,0,0),'%H:%i:%s') end))/60 d
from (SELECT
endTime
,startTime
,hour(startTime) sh
,hour(endTime) eh
FROM
dt) d
inner join ol on ol.n between d.sh and d.eh
) dane