MySQL dynamically find available reservations

Question

I have a requirement to create a room reservation system with dynamic booking slots (a reservation can start at any time and last anywhere from 15 minutes up to several days), but I can't crack the logic.

Reservations need to consider the opening times of the rooms (some rooms are based at sites that are available for 24 hours and others only a few hours per day. Some days, rooms may be entirely unavailable or have reduced opening hours). A reservation cannot jump across opening times with a gap greater than 1 second. For example, it is ok for a reservation to be accepted if the closing time on Sunday is 23:59:59 and it reopens at 00:00:00 on Monday, as this isn't considered a gap. However, if the opening time were 01:00:00 Monday, this would not be acceptable.

In addition, the database will have rooms across the world, operating in different time zones, so this will also need to be a consideration in the final design. Finally, a reservation cannot begin or end within 1 hour of another reservation.

Here is a stripped back schema of our current tables:

site

+---------------------------------------+-------+------------------------+---------------------------------------+
| uuid                                  | id    | name                   | address                               |
+---------------------------------------+-------+------------------------+---------------------------------------+
| 418bbdcb-09c9-4407-adb4-162fb3e5e6f3  | 1     | NAMCO Library          | 6 London Rd, Los Angeles, USA, 087098 |
+---------------------------------------+-------+------------------------+---------------------------------------+
| 418bbdcb-09c9-4407-adb4-162fb3e5e6f6  | 2     | Livingston Hotel       | 6 Jump Street, London, UK, E56JK      |
+---------------------------------------+-------+------------------------+---------------------------------------+

opening_times

+----+----------+--------------+-----------+-------------+---------------+-----------------------+
| id | site_id  | day_of_week  | open      | close       | date_outlier  | timezone              |
+----+----------+--------------+-----------+-------------+---------------+-----------------------+
| 1  | 1        | 1            | 10:00:00  | 15:59:59    | NULL          | America/Los_Angeles   |
+----+----------+--------------+-----------+-------------+---------------+-----------------------+
| 2  | 1        | 2            | 07:00:00  | 21:59:59    | NULL          | America/Los_Angeles   |
+----+----------+--------------+-----------+-------------+---------------+-----------------------+
| 3  | 1        | 3            | 07:00:00  | 21:59:59    | NULL          | America/Los_Angeles   |
+----+----------+--------------+-----------+-------------+---------------+-----------------------+
| 4  | 1        | 4            | 07:00:00  | 21:59:59    | NULL          | America/Los_Angeles   |
+----+----------+--------------+-----------+-------------+---------------+-----------------------+
| 5  | 1        | 5            | 07:00:00  | 21:59:59    | NULL          | America/Los_Angeles   |
+----+----------+--------------+-----------+-------------+---------------+-----------------------+
| 6  | 1        | 6            | 07:00:00  | 21:59:59    | NULL          | America/Los_Angeles   |
+----+----------+--------------+-----------+-------------+---------------+-----------------------+
| 7  | 1        | 7            | 10:00:00  | 22:59:59    | NULL          | America/Los_Angeles   |
+----+----------+--------------+-----------+-------------+---------------+-----------------------+
| 8  | 2        | 1            | 00:00:00  | 23:59:59    | NULL          | Europe/London         |
+----+----------+--------------+-----------+-------------+---------------+-----------------------+
| 9  | 2        | 2            | 00:00:00  | 23:59:59    | NULL          | Europe/London         |
+----+----------+--------------+-----------+-------------+---------------+-----------------------+
| 10 | 2        | 3            | 00:00:00  | 23:59:59    | NULL          | Europe/London         |
+----+----------+--------------+-----------+-------------+---------------+-----------------------+
| 11 | 2        | 4            | 00:00:00  | 23:59:59    | NULL          | Europe/London         |
+----+----------+--------------+-----------+-------------+---------------+-----------------------+
| 12 | 2        | 5            | 00:00:00  | 23:59:59    | NULL          | Europe/London         |
+----+----------+--------------+-----------+-------------+---------------+-----------------------+
| 13 | 2        | 6            | 00:00:00  | 23:59:59    | NULL          | Europe/London         |
+----+----------+--------------+-----------+-------------+---------------+-----------------------+
| 14 | 2        | 7            | 00:00:00  | 23:59:59    | NULL          | Europe/London         |
+----+----------+--------------+-----------+-------------+---------------+-----------------------+
| 15 | 2        | NULL         | 10:30:00  | 16:59:59    | 2022-05-04    | Europe/London         |
+----+----------+--------------+-----------+-------------+---------------+-----------------------+
| 16 | 2        | NULL         | NULL      | NULL        | 2022-05-05    | Europe/London         |
+----+----------+--------------+-----------+-------------+---------------+-----------------------+

All times in this table are in local timezones. Row 15 on this table shows a reduced opening time at the Livingston Hotel on May 4th, 2022. Row 16 on this table shows that the Livingston Hotel does not allow any bookings on May 5th, 2022.

rooms

+---------------------------------------+-------+---------+------------------------+-------------+-----------+
| uuid                                  | id    | site_id | name                   | hourly_cost | currency  |
+---------------------------------------+-------+---------+------------------------+-------------+-----------+
| 418bbdcb-09c9-4407-adb4-162fb3e5e6d3  | 1     | 1       | Blue Room              | 60.00       | USD       |
+---------------------------------------+-------+---------+------------------------+-------------+-----------+
| 418bbdcb-09c9-4407-adb4-162fb3e5e6d6  | 2     | 1       | Red Room               | 35.50       | USD       |
+---------------------------------------+-------+---------+------------------------+-------------+-----------+
| 418bbdcb-09c9-4407-adb4-162fb3e5e6d7  | 3     | 1       | Purple Room            | 15.50       | USD       |
+---------------------------------------+-------+---------+------------------------+-------------+-----------+
| 418bbdcb-09c9-4407-adb4-162fb3e5e6d8  | 4     | 2       | Dragon Room            | 55.50       | GBP       |
+---------------------------------------+-------+---------+------------------------+-------------+-----------+
| 418bbdcb-09c9-4407-adb4-162fb3e5e6d9  | 5     | 2       | Bus Room               | 85.00       | GBP       |
+---------------------------------------+-------+---------+------------------------+-------------+-----------+

reservations

+---------------------------------------+---------+-------------+----------------------+---------------------+--------------+
| uuid                                  | room_id | customer_id | start                | end                 | cancelled_at |
+---------------------------------------+---------+-------------+----------------------+---------------------+--------------+
| 418bbdcb-09c9-4407-adb4-162fb3e5e6h3  | 3       | 1           | 2022-05-04 14:01:53  | 2022-05-01 14:16:53 | NULL         |
+---------------------------------------+---------+-------------+----------------------+---------------------+--------------+
| 418bbdcb-09c9-4407-adb4-162fb3e5e6h4  | 4       | 3           | 2022-05-03 11:01:53  | 2022-05-03 12:01:53 | NULL         |
+---------------------------------------+---------+-------------+----------------------+---------------------+--------------+

Start and end times in this table are stored in UTC.

The SQL that we currently use does not correctly deal with reservations spanning different days and has no consideration of timezone conversions for opening times.

We're using prepared statements to replace the following strings in the SQL.

:start is the start time of the reservation in UTC :end is the end time of the reservation in UTC

SELECT 
            sites.id site_id,
            sites.site_ref,
            sites.name site_name,
            rooms.name room_name,
            
        FROM 
            sites
        JOIN
            rooms
            ON
                rooms.site_id = sites.id 
        LEFT JOIN 
            reservations bookings 
            ON 
                rooms.id = reservations.room_id 
                AND (
                    (:start + INTERVAL 1 HOUR) <= reservations.end
                    AND 
                    (:end - INTERVAL 1 HOUR)  >= reservations.start
                    AND
                    reservations.cancelled_at IS NULL
                )

        JOIN
            opening_times
        ON
            sites.id = opening_times.site_id
            AND (
                opening_times.site_id NOT IN (
                    SELECT
                        site_id
                    FROM
                        tbl_opening_times
                    WHERE
                        DATE(:start) = date_outlier AND open IS NULL AND close IS NULL
                ) OR (
                    (DATE(:start) = opening_times.date_outlier AND TIME(:start) >= opening_times.open AND TIME(:end) <= opening_times.close)
                    OR
                    (DAYOFWEEK(DATE(:start)) = opening_times.dow AND TIME(:start) >= opening_times.open AND TIME(:end) <= opening_times.close)
                )
            )
        WHERE
                reservations.uuid IS NULL
        ORDER BY
            sites.id

Tests

1. A query for a reservation lasting 3 hours starting at 2022-05-04 14:00:00 (UTC) should list room id 1,2 at NAMCO Library. The rooms at the Livingston Hotel should not be listed as the end of the reservation will happen after the particular closing time of the hotel.

2. A query for a reservation lasting two days starting at 2022-05-01 12:30:00 (UTC) should list room id five at Livingston Hotel.

3. A query for a reservation lasting 40 minutes starting at 2022-05-05 15:30:00 (UTC) should list room id 1,2,3 at NAMCO Library.

I'm open to solutions that involve processing on the application side (we use PHP) if the complexity is too much for MySQL alone.

FYI we're using MySQL 8.*