CODEWITHSUNDEEP
mysqlsqlselect
Aaron Arima
Aaron Arima

Reputation: 184

Select query that outputs counts of rows that are within a certain time period

My question is little bit more complicated than what the title implies but here it is:

I have a table with punch data formatted like this:

name time_in              time_out             location
1    2018-05-31 10:09:00  2018-05-31 16:06:00  1
3    2018-05-31 10:12:00  2018-05-31 17:03:00  1

I would like a select query that returns the total time in hours of people that are working during a 15min time frame for each location. Here is an example output for the two rows given:

time                 labor_hours  location
2018-05-31 10:00:00  .15          1
2018-05-31 10:15:00  .50          1
2018-05-31 10:30:00  .50          1
2018-05-31 10:45:00  .50          1
2018-05-31 11:00:00  .50          1
...
2018-05-31 15:45:00  .50          1
2018-05-31 16:00:00  .35          1
2018-05-31 16:15:00  .25          1
2018-05-31 16:30:00  .25          1
2018-05-31 16:45:00  .25          1
2018-05-31 17:00:00  .10          1

Labor hours is total hours worked during a 15min time period in hours. So for example, the first row was calculated by looking at the first two rows and seeing that from 10:00:00 - 10:15:00 employee 1 and 2 worked for a total of 9 minutes. Since it's in hours 9/60 = .15.

I'm new to sql so I'm pretty lost on how to start with this.

Upvotes: 1

Views: 107

Answers (1)

Peeyush
Peeyush

Reputation: 726

If you are using MySQL 8.0, you can use the CTE feature as follows

WITH cte (timeStamp) AS
(
  SELECT "2018-05-31 00:00:00"
  UNION ALL
  SELECT TIMESTAMPADD(MINUTE, 15, timeStamp) 
  WHERE timeStamp < 2018-06-01 00:00:00
)
SELECT timeStamp FROM cte;

SELECT cte.time, 
sum(TIMESTAMPDIFF(MINUTE, punch.time, 
                  TIMESTAMPAD(MINUTE, 15, cte.timeStamp))) as labour_hours, 
punch.location 
FROM cte LEFT OUTER JOIN punch ON punch.time >=cte.timeStamp 
AND punch.time < TIMESTAMPADD(MINUTE, 15, cte.timeStamp)
GROUP BY punch.location, cte.timeStamp

If you are using an older version of MySQL, you need to create a stored procedure that generates the timestamps with 15 minute intervals.

Upvotes: 2

Related Questions