PostgreSQL – Checking if Date Interval Does Not Overlap in Array of Intervals

postgresqlpostgresql-9.6

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
)

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, example: ('2018-11-21 12:00','2018-11-23 12:00'),('2018-11-19 12:00','2018-11-20 12:00').

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);

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? Thanks

Best Answer

Why don’t you use second table “rooms_reservations” with period of data reservation? It’s more easy and faster with big data.