How to make time buckets with a start and end time column?

Question

I have 3 columns, employee_id, start_time and end_time I want to make bucks of 1 hour to show me how many employees were working in each hour. For example, employee A worked from 12 pm to 3 pm and employee B worked from 2 pm to 4 pm so, at 12 pm (1 employee was working) 1 pm (1 employee) 2 pm (2 employees were working) 3 pm (2 employees) and 4 pm (1 employee), how can I make this in SQL? Let me show you a picture of the start and end time columns.

Sample input would be:

Expected outcome would be something like

I want to create a bucket in order to know how many people were working in each hour of the day.

SELECT

  Employee_id,
  TIME(shift_start_at,timezone) AS shift_start,
  TIME(shift_end_at,timezone) AS shift_end,
  
FROM
  `employee_shifts` AS shifts
 
WHERE
  DATE(shifts.shift_start_at_local) >= "2022-05-01"
  
GROUP BY
  1,
  2,
  3

Answer 1

Assuming you are on mysql version 8 or above generate all the buckets , left join to shifts to infill times in start-endtime ranges , filter out those that are not applicable then count eg:-

DROP TABLE IF EXISTS t;

create table t (id int, startts datetime, endts datetime);

insert into t values
(1,'2022-06-19 08:30:00','2022-06-19 10:00:00'),
(2,'2022-06-19 08:30:00','2022-06-19 08:45:00'),
(3,'2022-06-19 07:00:00','2022-06-19 07:59:00');

 with cte as 
(select 7 as bucket union select 8 union select 9 union select 10 union select 11),
cte1 as
(select bucket,t.*,
             floor(hour(startts)) starthour, floor(hour(endts)) endhour
from cte
left join t on cte.bucket between floor(hour(startts)) and floor(hour(endts)) 
) 
select bucket,count(id) nof from cte1 group by bucket
;
+--------+-----+
| bucket | nof |
+--------+-----+
|      7 |   1 |
|      8 |   2 |
|      9 |   1 |
|     10 |   1 |
|     11 |   0 |
+--------+-----+
5 rows in set (0.001 sec)