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.