SQL: how to select the time range of an employee and group by timeframe interval 30mins

Question

I have three columns, time-in(timestamp), time-out(timestamp) and employee. I need to get the number of employees that work in a specific timeframe (30min interval). For example:

    employee_id              timein              timeout
    101                      10:10               12:59
    102                       9:07               12:16
    103                      11:16               12:08

I need a query that will give me this result

    timeframe         count(employee_id)
    09:00                    1
    09:30                    1
    10:00                    2
    10:30                    2
    11:00                    3
    11:30                    3
    12:00                    3
    12:30                    1

I really hope I made it clear. Thanks

May we ask why there are no dates present? What happens if an employee works the night shift from `23:00` to `07:00`? — Tim Biegeleisen, Dec 02 '17 at 07:01
Hi im sorry, i just did it this way to make it quicker. There are no night shifts too. Its only from 8am to 11pm. — hahahahahelp, Dec 02 '17 at 07:05

krokodilko · Answer 1 · 2017-12-02T07:42:25.500

See this demo: http://sqlfiddle.com/#!17/2477f/1

SELECT x.timeframe, count(employee_id)
FROM (
   select time '8:00' + x * interval '30 minute' as timeframe,
          time '8:00' + (x+1) * interval '30 minute' as timeframe_end
   from generate_series(0,10) x
) x
LEFT JOIN employee t
/* (StartA <= EndB) and (EndA >= StartB) */
ON x.timeframe <= t.timeout
AND x.timeframe_end >= t.timein
GROUP BY x.timeframe
ORDER BY 1

SELECT x.timeframe, count(employee_id)
FROM (
   select time '8:00' + x * interval '30 minute' as timeframe,
          time '8:00' + (x+1) * interval '30 minute' as timeframe_end
   from generate_series(0,12) x
) x
LEFT JOIN employee t
/* (StartA < EndB) and (EndA > StartB) */
ON x.timeframe < t.timeout
AND x.timeframe_end > t.timein
GROUP BY x.timeframe
ORDER BY 1
| timeframe | count |
|-----------|-------|
|  08:00:00 |     0 |
|  08:30:00 |     0 |
|  09:00:00 |     1 |
|  09:30:00 |     1 |
|  10:00:00 |     2 |
|  10:30:00 |     2 |
|  11:00:00 |     3 |
|  11:30:00 |     3 |
|  12:00:00 |     3 |
|  12:30:00 |     1 |
|  13:00:00 |     1 |
|  13:30:00 |     1 |
|  14:00:00 |     0 |

The join condition uses a formula from this answer for checking whether two ranges overlap or not:

(StartA < EndB) and (EndA > StartB)

The demo also shows how the query behaves for edge cases:

(113, '13:00', '13:01'),
(115, '13:30', '14:00')

The latter employe started at 13:30 and finished at 14:00, so it is included in 13:30 timeframe, but is not included in 14:00 timeframe.

|  13:00:00 |     1 |
|  13:30:00 |     1 |
|  14:00:00 |     0 |

The problem might be with employes that start and finish a work multiple times within the same timeframe (workers who make frequent coffee breaks), for example:

(113, '13:00', '13:01'),
(113, '13:12', '13:15'),
(113, '13:22', '13:26')

for such cases you need to count distinct employees, using: count(DISTINCT employee_id)

Hi this works so well! Btw, what if i remove the timeout table, just to see how many people timein during the same timeframe(30min interval) how can i edit this? — hahahahahelp, Dec 02 '17 at 13:41

Kaushik Nayak · Answer 2 · 2017-12-02T07:16:44.107

0

Try something like this.

SELECT timeframe,
COUNT (employee_id)
FROM employee a
RIGHT JOIN
(SELECT *
 FROM generate_series (TIMESTAMP '2017-09-01 09:00:00', 
                       TIMESTAMP '2017-09-01 17:00:00', 
               INTERVAL '0.5 HOUR' ) AS timeframe) b 
                 ON b.timeframe >=  timein
                 AND b.timeframe <= timeout
GROUP BY timeframe
ORDER BY timeframe ;

edited Dec 02 '17 at 07:16

answered Dec 02 '17 at 07:11

Kaushik Nayak

30,772
5
32
45

This works! But is there a way to make them round off by 15mins? For example: a guy ended his shift at 12:02, it will register on the 12:30 group. Is there a way to make this like this? endshift: 12:02 = 12:00, 12:16 = 12:30 – hahahahahelp Dec 02 '17 at 07:28
@RodDivinagracia : Refer this link to get some idea : https://stackoverflow.com/questions/6195439/postgres-how-do-you-round-a-timestamp-up-or-down-to-the-nearest-minute – Kaushik Nayak Dec 02 '17 at 07:44
how about when i try to do this with one timestamp? for example: number of time-ins for the same period. i tried editing your query but it gives me very big values. – hahahahahelp Dec 02 '17 at 12:37

score 0 · Answer 3 · answered Dec 02 '17 at 07:21

SELECT out_time-in_time time_frame, count(*) FROM
TABLE_NAME GROUP BY out_time-in_time

I tested against a sample local data.

employee_id | in_time  | out_time 
-------------+----------+----------
         101 | 09:07:00 | 12:08:00
         102 | 10:07:00 | 17:08:00
         103 | 12:07:00 | 17:08:00
         104 | 12:07:00 | 17:08:00
         105 | 10:07:00 | 17:08:00

Output from the query.

time_frame | count 
------------+-------
 07:01:00   |     2
 03:01:00   |     1
 05:01:00   |     2

You can include rounding off logic accordingly on finding the difference.

score 0 · Answer 4 · answered Dec 02 '17 at 08:19

SQL Fiddle

PostgreSQL 9.6 Schema Setup:

CREATE TABLE emp_time
    ("employee_id" int, "timein" time, "timeout" time)
;

INSERT INTO emp_time
    ("employee_id", "timein", "timeout")
VALUES
    (101, '10:10', '12:59'),
    (102, '9:07', '12:16'),
    (103, '11:16', '12:08')
;

Query 1:

SELECT 
      slot_start
    , slot_end
    , count(employee_id)
FROM  (
      SELECT slot_start, slot_start + INTERVAL '30 MINUTE' slot_end
      FROM generate_series (TIMESTAMP '2017-01-01 09:00:00', TIMESTAMP '2017-01-01 16:30:00', INTERVAL '30 MINUTE' ) AS slot_start
      ) t 
LEFT JOIN emp_time et ON et.timein < t.slot_end::time and et.timeout > t.slot_start::time
GROUP BY
      slot_start
    , slot_end
ORDER BY
      slot_start
    , slot_end
;

Results:

|           slot_start |             slot_end | count |
|----------------------|----------------------|-------|
| 2017-01-01T09:00:00Z | 2017-01-01T09:30:00Z |     1 |
| 2017-01-01T09:30:00Z | 2017-01-01T10:00:00Z |     1 |
| 2017-01-01T10:00:00Z | 2017-01-01T10:30:00Z |     2 |
| 2017-01-01T10:30:00Z | 2017-01-01T11:00:00Z |     2 |
| 2017-01-01T11:00:00Z | 2017-01-01T11:30:00Z |     3 |
| 2017-01-01T11:30:00Z | 2017-01-01T12:00:00Z |     3 |
| 2017-01-01T12:00:00Z | 2017-01-01T12:30:00Z |     3 |
| 2017-01-01T12:30:00Z | 2017-01-01T13:00:00Z |     1 |
| 2017-01-01T13:00:00Z | 2017-01-01T13:30:00Z |     0 |
| 2017-01-01T13:30:00Z | 2017-01-01T14:00:00Z |     0 |
| 2017-01-01T14:00:00Z | 2017-01-01T14:30:00Z |     0 |
| 2017-01-01T14:30:00Z | 2017-01-01T15:00:00Z |     0 |
| 2017-01-01T15:00:00Z | 2017-01-01T15:30:00Z |     0 |
| 2017-01-01T15:30:00Z | 2017-01-01T16:00:00Z |     0 |
| 2017-01-01T16:00:00Z | 2017-01-01T16:30:00Z |     0 |
| 2017-01-01T16:30:00Z | 2017-01-01T17:00:00Z |     0 |

SQL: how to select the time range of an employee and group by timeframe interval 30mins

4 Answers4