Fetch empty/open time ranges in postgresql

Question

I'm trying to find open shifts where:

First shift starts at 6 AM
Last Shift ends at 12 AM

ie:

Given the following data/day:

start_time | end_time
-----------|---------
9  AM      |  3  PM
5  PM      |  10 PM

Expected results:

start_time | end_time
-----------|---------
6  AM      | 9  AM
3  PM      | 5  PM
10 PM      | 12 AM

Here's what I tried but it's not working (Ik it's mostly way far from the correct answer)

SELECT *
FROM WORKERS_SCHEDULE
WHERE START_TIME not BETWEEN 
    ANY (SELECT START_TIME FROM WORKERS_SCHEDULE)
    AND (SELECT START_TIME FROM WORKERS_SCHEDULE)

start_time and end_time are of datatype TIME.

I left a simple sample and desired results after it @GordonLinoff — Marsilinou Zaky
– Marsilinou Zaky, Commented Jan 19, 2020 at 19:26
What is the data type of start_time and end_time? Is it a timestamp or a varchar or something else? — Ladi Oyeleye
– Ladi Oyeleye, Commented Jan 19, 2020 at 19:31
Updated the question, they're type time, thanks @LadiOyeleye — Marsilinou Zaky
– Marsilinou Zaky, Commented Jan 19, 2020 at 19:34

GMB · Accepted Answer · 2020-01-19 21:11:22Z

2

Here is one way to do it with union all and window functions:

select *
from (

    select '06:00:00'::time start_time, min(start_time) end_time from mytable
    union all 
    select end_time, lead(start_time) over(order by start_time) from mytable
    union all 
    select max(end_time), '23:59:59'::time from mytable
) t
where start_time <> end_time

It is bit complicated to thouroughly explain how it works but: the first unioned query computes the interval between 6 AM and the start of the first shift, the second subquery processes declared shift, and the last one handles the interval between the last shift and midnight. Then, the outer query filters on records that have gaps. To understand how it works, you can run the subquery independently, and see how the starts and ends ajust.

Demo on DB Fiddle:

start_time | end_time
:--------- | :-------
06:00:00   | 09:00:00
15:00:00   | 17:00:00
22:00:00   | 23:59:59

answered Jan 19, 2020 at 21:11

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Marsilinou Zaky Over a year ago

Awsome that worked, thanks for the explanation as well that made complete sense :)

Himanshu · Accepted Answer · 2020-01-19 20:11:31Z

0

Try this if it works for you

SELECT  
  Case when 
      START_TIME=(SELECT 
         MIN(start_time) FROM 
       TABLE) AND START_TIME >'6:00 
        AM' 
        THEN 
         '6:00 AM -' ||MIN(START_TIME) 
         ELSE

         SELECT min(END_TIME) FROM 
         TABLE  
         WHERE  ENDTIME<S.START_time
          ||StartTime  
     End 
         From table S

        Union 
      (select max(endtime) ||
        ' 12:00 AM' from table)

edited Jan 19, 2020 at 20:11

answered Jan 19, 2020 at 20:05

Himanshu

3,9902 gold badges16 silver badges34 bronze badges

Comments

Ladi Oyeleye · Accepted Answer · 2020-01-19 21:29:02Z

This is another way:

WITH RECURSIVE open_shifts AS (
  SELECT time '6:00' AS START_TIME, MIN(START_TIME) AS END_TIME 
    FROM WORKERS_SCHEDULE
    WHERE START_TIME BETWEEN time '6:00' AND time '23:59'
  UNION
  SELECT start_gap.START_TIME AS START_TIME, end_gap.END_TIME AS END_TIME FROM WORKERS_SCHEDULE end_gap,
  (SELECT ws.END_TIME AS START_TIME
       FROM WORKERS_SCHEDULE ws, open_shifts prev_gap
       WHERE ws.START_TIME = prev_gap.END_TIME) start_gap
  WHERE end_gap.END_TIME > start_gap.START_TIME
  AND END_TIME BETWEEN time '6:00' AND time '23:59'
) 
SELECT * FROM open_shifts
UNION
SELECT MAX(END_TIME) AS START_TIME, time '23:59' AS END_TIME FROM WORKERS_SCHEDULE
WHERE END_TIME BETWEEN time '6:00' AND time '23:59';

Used a recursive CTE to find the gaps between each shift's end time and the next closest shift's start time. This probably won't work with overlapping shifts though.

Collectives™ on Stack Overflow

Fetch empty/open time ranges in postgresql

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related