How to make time buckets with a start and end time colum SQL?-CodePudding

I have 3 columns, employee_id, start_time and end_time i want to make bucks of 1 hours to show me how many employees were working in each hour. For example, employee A worked from 12pm to 3pm and employee B worked from 2pm to 4pm so, at 12m (1 employee was working) 1pm (1 employee) 2pm (2 employees were working) 3pm (2employees) and 4pm (1epmloyees), 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

CodePudding user response：

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)

CodePudding user response：

Just use a subquery for each column mentioning the required timestamp in between, also make sure your start_time and end_time columns are timestamp types. For more information, please share the table structure, sample data, and expected output

CodePudding user response：

If I understood well, this would be

SELECT HOUR, (SELECT COUNT(*)
  FROM employee
 WHERE start_time <= HOUR
   AND end_time   >= HOUR) AS  working
  FROM schedule HOUR

Where schedule is a table with employee schedules.