sql - Check if hour range is not in 'lunch break' hour range in PostgreSQL

Question

I want to get offices that are available.

Lets example I rent a car and I want to know the office is open at pickup/dropoff time.

I want to find the offices that open in after open_time and before end_time.

Office table;

+---+-----------+
|id |office_name|
+---+-----------+
|116|  AAAAA    |
|117|  BBBBB    |
|118|  CCCCC    |
|119|  DDDDD    |    
|120|  EEEEE    |     
|121|  FFFFF    |
|122|  GGGGG    |
+---+-----------+

Workhour table;

+----+---------+-----------+---------+----------+
|id  |office_id|day_of_week|open_time|close_time|
|1325|116      |4          |09:00:00 |12:00:00  | <- 116 has 1 break (available)
|1326|116      |4          |13:00:00 |18:00:00  |  
|1327|117      |4          |09:00:00 |18:00:00  | <- 117 has no break (available)
|1328|118      |4          |09:00:00 |12:00:00  | <- 118 has 3 break (available)
|1329|118      |4          |13:00:00 |15:00:00  | 
|1330|118      |4          |17:00:00 |20:00:00  | 
|1331|119      |4          |09:00:00 |13:30:00  | <- not available
|1332|220      |4          |15:00:00 |17:30:00  | <- not available
|1332|221      |4          |06:00:00 |17:30:00  | <- not available
|1332|222      |4          |09:00:00 |17:30:00  | <- 222 has no break (available)
+----+------------------+-----------+-----------+

I can get office when the office has 1 workhour row but I couldn't manage to make a query for if the office has more than 1 work hour range.

SELECT supplier_offices.id          as office_id,
       supplier_offices.supplier_id as supplier_id,
       supplier_offices.name        as supplier_office_label,
FROM "supplier_offices"
         INNER JOIN office_locations on supplier_offices.id = office_locations.supplier_office_id
         INNER JOIN supplier_office_work_hours as wh on supplier_offices.id = wh.supplier_office_id
  AND (supplier_offices.status = true)
  AND ("office_locations"."location_id" IN (3027))
  AND (wh.day_of_week = 4)
  AND (wh.open_time <= '09:01'::time)
  AND ('14:00'::time <= wh.close_time)

I got:

+---+-----------+
|id |office_name|
+---+-----------+
|119|  DDDDD    | <- 119 has no break    
+---+-----------+

Expected:

All offices are available between 09:01 - 14:00

+---+-----------+
|id |office_name|
+---+-----------+
|116|  AAAAA    |
|117|  BBBBB    |
|118|  CCCCC    |
|122|  GGGGG    |
+---+-----------+

question from:https://stackoverflow.com/questions/66055581/check-if-hour-range-is-not-in-lunch-break-hour-range-in-postgresql

Answer 1

Your information is a little skimpy but if I understand you want both sets of hours on a single row. You can convert the time columns to a string and concatenate open/close columns. Then use STRING_AGG.

select supplier_office_id
     ,  string_agg(to_char(open_time,'hh24:mi')||'-'||to_char(close_time,'hh24:mi'), ' | ') as hours
 from office
 group by supplier_office_id;

Example Here

If this is not what you are wanting please edit the question to provide fully explain what you want and include desired output. Ans full table definitions (DDL).