Group result into hourly subgroups

Question

I have a query which results in total booked time on a resource in a given period of time/days. I want to split the total amount booked to be grouped by the hour.

SELECT
  resourceID,
  SEC_TO_TIME(
    SUM(
      CASE WHEN DATEDIFF(DATE(endDatetime), DATE(startDatetime)) = 0 THEN
        GREATEST(TIME_TO_SEC(TIMEDIFF(LEAST('16:00:00', TIME(endDatetime)), LEAST(GREATEST('08:00:00', TIME(startDatetime)), '16:00:00'))), 0)
      ELSE
        TIME_TO_SEC(TIMEDIFF('16:00:00', LEAST(GREATEST('08:00:00', TIME(startDatetime)), '16:00:00')))
        + TIME_TO_SEC(TIMEDIFF(GREATEST('08:00:00', LEAST('16:00:00', TIME(endDatetime))), '08:00:00'))
        + ((DATEDIFF(DATE(endDatetime), DATE(startDatetime)) - 1) * TIME_TO_SEC(TIMEDIFF('16:00:00', '08:00:00')))
      END
    )
  ) AS booked_time
FROM table_name
WHERE resourceID = 1
AND startDatetime BETWEEN '2019-01-01 00:00:00' AND '2019-01-05 23:59:59'
GROUP BY resourceID

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=dbacebf2b5aa3ca8233bab7273f1363c

This returns the total booked time. I would like the result to be grouped per hour: Fx.

2019-01-01 08:00-09:00, BookedTime 0:30
2019-01-01 09:00-10:00, BookedTime 1:00
2019-01-01 10:00-11:00, BookedTime 0:30
2019-01-02 08:00-09:00, BookedTime 0:30
2019-01-02 09:00-10:00, BookedTime 1:00
2019-01-02 10:00-11:00, BookedTime 0:30

and so on.

EDITED:

I would also love a solution to the result below:

08:00-09:00, BookedTime 6:30
09:00-10:00, BookedTime 10:00
10:00-11:00, BookedTime 3:30

The difference is that you add the total booked time between each hour over multiple dates.

I guess you also want 0 for dates and time without any booking? — Juan Carlos Oropeza, Oct 17 '19 at 14:21
You have mutliple booking at `2019-01-01 04:30:00` so what result you want as BookedTime? still 30 min? or the sum of all the booking? — Juan Carlos Oropeza, Oct 17 '19 at 14:48
Yes, I want zero for dates and times without any bookings.It should be the sum of all bookings in that period. — Jeppe, Oct 17 '19 at 16:32
This is what I have [now](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=43e1dc128cdffcd083faa80f7f04617d&hide=28). I create a list of dates and times. Otherwise, you can't get values for when you don't have data. Also calculate the booking duration for each time slot — Juan Carlos Oropeza, Oct 17 '19 at 16:44

score 0 · Answer 1 · answered Oct 17 '19 at 16:52

SQL DEMO

First you need create a table for all your days and hours.

Notice 100 years of data need only 100 * 365 * 24 = 876000 rows You can select what range of date create on the WHERE condition

CREATE TABLE allDays as
select DATE_ADD(selected_date, INTERVAL start_time HOUR) as start_time
     , DATE_ADD(selected_date, INTERVAL end_time HOUR) as end_time
from 
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date 
 from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
 ) v
CROSS JOIN (
   SELECT 00 as start_time, 01 as end_time UNION 
   SELECT 01 as start_time, 02 as end_time UNION 
   SELECT 02 as start_time, 03 as end_time UNION 
   SELECT 03 as start_time, 04 as end_time UNION 
   SELECT 04 as start_time, 05 as end_time UNION 
   SELECT 05 as start_time, 06 as end_time UNION 
   SELECT 06 as start_time, 07 as end_time UNION 
   SELECT 07 as start_time, 08 as end_time UNION 
   SELECT 08 as start_time, 09 as end_time UNION 
   SELECT 09 as start_time, 10 as end_time UNION 
   SELECT 10 as start_time, 11 as end_time UNION 
   SELECT 11 as start_time, 12 as end_time UNION 
   SELECT 12 as start_time, 13 as end_time UNION 
   SELECT 13 as start_time, 14 as end_time UNION 
   SELECT 14 as start_time, 15 as end_time UNION 
   SELECT 15 as start_time, 16 as end_time UNION 
   SELECT 16 as start_time, 17 as end_time UNION 
   SELECT 17 as start_time, 18 as end_time UNION 
   SELECT 18 as start_time, 19 as end_time UNION 
   SELECT 19 as start_time, 20 as end_time UNION 
   SELECT 20 as start_time, 21 as end_time UNION 
   SELECT 21 as start_time, 22 as end_time UNION 
   SELECT 22 as start_time, 23 as end_time UNION 
   SELECT 23 as start_time, 24 as end_time 
)  t
where selected_date between '2018-12-31' and '2019-01-10'
;

Now you split all booking between those time slots

SELECT *
     , GREATEST( a.start_time , t.startDatetime ) as book_start
     , LEAST( a.end_time , t.endDatetime ) as book_end
     , TIMEDIFF( LEAST( a.end_time , t.endDatetime )
               , GREATEST( a.start_time , t.startDatetime ) ) as time_diff
FROM allDays a
LEFT JOIN table_name t
  ON a.start_time <= t.endDatetime
 AND a.end_time   >= t.startDatetime  
WHERE start_time < '2019-01-05 23:59:59'
  AND end_time   > '2019-01-01 00:00:00'
ORDER BY 1  
;

Now you can group each time slot and add the booked time

With time_slots as (
SELECT *
     , GREATEST( a.start_time , t.startDatetime ) as book_start
     , LEAST( a.end_time , t.endDatetime ) as book_end
     , TIMEDIFF( LEAST( a.end_time , t.endDatetime )
               , GREATEST( a.start_time , t.startDatetime ) ) as time_diff


FROM allDays a
LEFT JOIN table_name t
  ON a.start_time <= t.endDatetime
 AND a.end_time   >= t.startDatetime  
WHERE start_time < '2019-01-05 23:59:59'
  AND end_time   > '2019-01-01 00:00:00'
ORDER BY 1  
)
SELECT start_time
     , end_time
     , SEC_TO_TIME( SUM( TIME_TO_SEC( `time_diff` ) ) ) AS timeSum  
FROM time_slots
GROUP BY start_time, end_time
ORDER BY 1
;

Group result into hourly subgroups

1 Answers1