postgreSQL - Checking if one date interval does not overlap a date interval in array of intervals for each row

Question

PostgreSQL(9.6), I am trying to check whether a date interval

('2018-11-18 12:00','2018-11-20 12:00')

does not overlap any of the following date intervals in the array

{('2018-11-21 12:00','2018-11-23 12:00'),('2018-11-19 12:00','2018-11-20 12:00')}

The elements in the array are composite:

CREATE TYPE reservation AS (
    checkIn TIMESTAMP WITHOUT TIME ZONE,
    checkOut TIMESTAMP WITHOUT TIME ZONE
)

Here is the rooms table:

CREATE TABLE rooms (
rId roomId PRIMARY KEY,
hRef hotelId NOT NULL,
rNo roomNo NOT NULL,
rType roomType,
numPeople INTEGER,
rBedOptions roomBed[],
reservations reservation[],
priceNight FLOAT,
FOREIGN KEY (hRef) REFERENCES hotels(hId) ON UPDATE CASCADE ON DELETE SET NULL  
)

INSERT INTO rooms VALUES
('R001','H001','101','one-bedroom',1,
ARRAY[row('1 twin')::roomBed],
ARRAY[
      row('2018-11-21 12:00','2018-11-23 12:00')::reservation,
      row('2018-11-19 12:00','2018-11-20 12:00')::reservation],
450.5);

Basically I am trying to check whether a "room" is available for a selected time interval by checking if this interval does not overlap with any existing reservation date intervals ('2018-11-21 12:00','2018-11-23 12:00'),('2018-11-19 12:00','2018-11-20 12:00'). So far I have been successful in checking the first element of the array for each row by writing the following query:

SELECT * FROM rooms R 
WHERE R.reservations[1] IS null 
OR NOT (('2018-11-18 12:00','2018-11-20 12:00') 
OVERLAPS (R.reservations[1].checkIn, R.reservations[1].checkOut)) 
ORDER BY rid;

The problem is that I don't know how to check all elements in the array if there is more than one. Any ideas or suggestions?

Why do you want to save nested structures like arrays within a table? Why not use a join table in long format? I usually recommend to OPs not using multiple items within tables and data sets (e.g., Python, R). Save bit data points in columns in long row tables. Otherwise querying will be very complex. — Parfait
– Parfait, Commented Nov 18, 2018 at 15:33

Emilio Platzer · Accepted Answer · 2018-11-19 13:36:33Z

You can use `unnest` to transform array into row on the fly

SELECT * 
  FROM rooms R,
    LATERAL (SELECT bool_or(
                        ('2018-11-18 12:00','2018-11-20 12:00') 
                       OVERLAPS (periods.checkIn, periods.checkOut)
                   ) as someone_overlaps
              FROM unnest(R.reservations) periods
             ) ok
  WHERE someone_overlaps is not true
  ORDER BY rid;

Explain

For each row in this sentence you do a "Lateral" subselect to ask if it overlaps
Unnest array into several rows
Check the overlaps
Compute the OR of each overlap, take it in the someone_overlapas
List each row that not have someone_overlapas

Notes

Booleans in postgress have three states: true, false and null thats why is not the same not someone_overlaps that someone_overlaps is not true, in the first case the null value remains null in the seconds null is not true. The second is used because the reservations may be an empty array.
Lateral a keyword that allows to use previous tables (in the from list) to be used in the curren subselct
unnest a function that transforms array into row
you can see a living example at: https://dbfiddle.uk/?rdbms=postgres_10&fiddle=c070f0eeaf4206f0540d9187c5e874d3

Collectives™ on Stack Overflow

postgreSQL - Checking if one date interval does not overlap a date interval in array of intervals for each row

1 Answer 1

You can use `unnest` to transform array into row on the fly

Explain

Notes

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

You can use unnest to transform array into row on the fly

Explain

Notes

Comments

Your Answer

Sign up or log in

Post as a guest

Related

You can use `unnest` to transform array into row on the fly