PostgreSQL query to return free rooms for booking availability calender

user_9854726 0
Viewed 36

I need bit of a help in writing an SQL query. A simple scenario is that I have a table named BookedRooms in which three columns are used most, checkInDate and checkOutDate, both are of type timestamp and roomId which is a foreign key to the Rooms table. Now Rooms table has PK, name column and roomNo column.

This is BookedRooms table

+----+----------------------------+-------------------------+------------------+--+
| PK |          checkInDate       |            checkOutDate |           roomId |  |
+----+----------------------------+-------------------------+------------------+--+
|  1 |     2022-05-26T00:00:00Z   |   2022-05-29T00:00:00Z  |                2 |  |
|  2 |     2022-05-29T00:00:00Z   |   2022-05-30T00:00:00Z  |                3 |  |
+----+----------------------------+-------------------------+------------------+--+

This is Rooms table

+----+------------+-------------------+--+
| PK |      name  |            roomNo |  |
+----+------------+-------------------+--+
|  2 |     Deluxe |               102 |  |
|  3 |       King |               103 |  |
+----+------------+-------------------+--+

Now, i wanna write a query in which if i put the month number like 4 , it tells me name and roomNo of Rooms which are free for each particular day of the month.

The logic to check if a room is occupied is that, if for example room 102 has a checkin date of 03 of month April and checkout date of 06 of month April , then the query will not include this room in the result set until the checkout date has come, only for that date and onwards would it include room 102 in the result set, again until this room appears in another checkInDate column somewhere.

Thank you

postgresql
1 Answers

I recommend creating an exclusion constraint on bookedrooms. Not only can the GiST index that implements the constraint speed up the search you want, but it will also exclude double booking.

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE bookedrooms ADD EXCLUDE USING gist (
   tstzrange(checkindate, checkoutdate) WITH &&,
   roomid WITH =
);

The query you need is

SELECT roomno FROM bookedrooms
EXCEPT
SELECT roomno FROM bookedrooms
WHERE tstzrange(checkindate, checkoutdate) &&
      tstzrange(
         date_trunc('year', current_timestamp) + INTERVAL '1 month' * 4,
         date_trunc('year', current_timestamp) + INTERVAL '1 month' * (4 + 1)
      );

&& is the "overlaps" operator for ranges.

user_64643080
Related