LaVOZs

The World’s Largest Online Community for Developers

'; sql - Fetch empty/open time ranges in postgresql - LavOzs.Com

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.

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

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) 

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.

Related
Calculate relative time in C#
PostgreSQL “DESCRIBE TABLE”
How to get the current time in Python
What do 'real', 'user' and 'sys' mean in the output of time(1)?
Show tables in PostgreSQL
Convert a Unix timestamp to time in JavaScript
How to get current time and date in Android
Why is subtracting these two times (in 1927) giving a strange result?
How to exit from PostgreSQL command line utility: psql