Finding the Closest Unbooked Dates Using SQL

Question

Scenario

A user selects a date. Based on the selection I check whether the date & time is booked or not (No issues here).

If a date & time is booked, I need to show them n alternative dates. Based on their date and time parameters, and those proposed alternative dates have to be as close as to their chosen date as possible. The list of alternative dates should start from the date the query is ran on My backend handles this.

My Progress So Far

SELECT alternative_date
FROM GENERATE_SERIES(
    TIMESTAMP '2022-08-20 05:00:00',
    date_trunc('month', TIMESTAMP '2022-08-20 07:00:00') + INTERVAL '1 month - 1 day',
    INTERVAL '1 day'
) AS G(alternative_date)

WHERE NOT EXISTS(
    SELECT * FROM events T
    WHERE T.bookDate::DATE = G.alternative_date::DATE
)

The code above uses the GENERATE_SERIES(...) function in PSQL. It searches for all dates, starting from 2022-08-20, and up to the end of August. It specifically returns the dates which does not exist in the bookDate column (Meaning it has not yet been booked).

Problems I Need Help With

• When searching for alternative dates, I'm providing 3 important things
  • The user's preferred booking date, so I can suggest which other dates are close to him that he can choose? How would I go about doing this? It's the part where I'm facing most trouble.
  • The user's start and end times, so when providing a list of alternative dates, I can tell him, hey there's free space between 06 and 07 on the date 2022-08-22 for instance. I'm also facing some issues here, a push in the right track will be great!
  • I want to add another WHERE but it fails, the current WHERE is a NOT EXISTS so it looks for all dates not equaling to what is given. My other WHERE basically means WHERE the place is open for booking or not.

Answer 1

To get closest free dates, you can ORDER BY your result by "distance" of particular alternative date to user's preferred date - the shortest intervals will be first:

ORDER BY alternative_date - TIMESTAMP '2022-08-20 05:00:00'

If you want to recommend time slots smaller than whole dates (hour range), you need to switch the whole thing from dates to hours, i.e. generate_series from 1 day to 1 hour (or whatever your smallest bookable unit is) and excluse invalid hours (nighttime I assume) in WHERE. From there, it is pretty much the same as with dates.

As for "second where", there can be only one WHERE, but it can be composed from multiple conditions - you can add more conditions using AND operator (and it can also be sub-query if needed):

WHERE NOT EXISTS(
    SELECT * FROM events T
    WHERE T.bookDate::DATE = G.alternative_date::DATE
) AND NOT EXISTS (
    SELECT 1 FROM events WHERE "roomId" = '13b46460-162d-4d32-94c0-e27dd9246c79'
)

(warning: this second sub-query is probably dangerous in real world, since the room will be used more than one time, I assume, so you need to add some time condition to the subquery to check against date)