CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.6
RollerMobster
RollerMobster

Reputation: 984

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

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?

Upvotes: 0

Views: 367

Answers (1)

Emilio Platzer
Emilio Platzer

Reputation: 2469

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

  1. For each row in this sentence you do a "Lateral" subselect to ask if it overlaps
  2. Unnest array into several rows
  3. Check the overlaps
  4. Compute the OR of each overlap, take it in the someone_overlapas
  5. 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

Upvotes: 1

Related Questions