Get 1 hour incrementation for room availability MySQL

Question

I am trying to get the availability of the rooms in my hotel by 1 hour incrementation. So for example, if the room is booked from 9 AM to 10 AM, and from 12 AM to 3 PM, I am trying to get the 1 hour increments of all other times between available_from to available_to

I am able to left join on the table and get the room availability but just not the time slots.

Here is my related schema:

Hotel:

Id | name

Reservation:

Id | hotel_id | room_id | start | end | status

Rooms:

Id | hotel_id | name | number | available_from | available_to

Here is the query I have so far:

SELECT r.id, r.name, r.number, r.type, r.rating
FROM rooms r
    LEFT OUTER JOIN reservations res ON res.room_id = r.id 
        AND CURRENT_TIMESTAMP BETWEEN r.available_from AND r.available_to
GROUP BY r.id, r.type

Example:

(This is the array I am trying to get back from database. Ignore the property names):

[{"roomNumber":1,"availableTimes":["2019-01-01 00:00:00","2019-01-01 01:00:00","2019-01-01 02:00:00","2019-01-01 03:00:00","2019-01-01 04:00:00","2019-01-01 05:00:00","2019-01-01 06:00:00","2019-01-01 07:00:00","2019-01-01 08:00:00","2019-01-01 09:00:00","2019-01-01 10:00:00","2019-01-01 11:00:00","2019-01-01 12:00:00","2019-01-01 13:00:00","2019-01-01 14:00:00","2019-01-01 15:00:00","2019-01-01 16:00:00","2019-01-01 17:00:00","2019-01-01 18:00:00","2019-01-01 19:00:00","2019-01-01 20:00:00","2019-01-01 21:00:00","2019-01-01 22:00:00","2019-01-01 23:00:00"]}]

I tried the following:

SELECT free_from, free_until
FROM (
  SELECT a.end AS free_from,
  (SELECT MIN(c.start)
   FROM reservations c
   WHERE c.start > a.end) as free_until
  FROM reservations a
  WHERE NOT EXISTS (
    SELECT 1
    FROM reservations b
    WHERE b.start BETWEEN a.end AND a.end + INTERVAL 1 HOUR
  )
  AND a.end BETWEEN '2019-01-03 09:00' AND '2019-01-03 21:00'
) as d
ORDER BY free_until-free_from
LIMIT 0,3;

But I get one row returned only with 1 result which is incorrect as well. How can I solve this problem?

Sample Data:

Hotel:

1 | Marriott

Reservation:

1 | 1 | 1 | 2019-01-03 15:00:00 | 2019-01-03 17:00:00 | Confirmed
1 | 1 | 1 | 2019-01-03 18:00:00 | 2019-01-03 20:00:00 | Confirmed

Rooms:

1 | 1 | "Single" | 528 | 09:00:00 | 21:00:00

Expected Result

Room Id | Room name | Available Times

1 | "Single" | 2019-01-03 09:00:00, 2019-01-03 10:00:00, 2019-01-03 11:00:00, 2019-01-03 12:00:00, 2019-01-03 13:00:00, 2019-01-03 14:00:00, 2019-01-03 17:00:00, 2019-01-03 20:00:00, 2019-01-03 21:00:00, 2019-01-03 22:00:00, 2019-01-03 23:00:00, 2019-01-03 24:00:00

I couldn't clearly understand, I appreciate if you put some example data and expected result. — Eray Balkanli, Dec 31 '18 at 15:43
So in your example, the room: 1 is available all day for 2019-01-01, right? @HeelMega — Eray Balkanli, Dec 31 '18 at 15:48
We have available_from and available_to in Rooms table, also Start and End fields in the Reservations table, what are their differences, why do we need Reservations table? In your example query you are not using any field from Reservations table too? @HeelMega — Eray Balkanli, Dec 31 '18 at 15:51
available_from and available_to is the time range the rooms are available. It wont be a all day room depending on the room. Reservation start and end is how long the room is reserved for in that reservation @ErayBalkanli — HeelMega, Dec 31 '18 at 15:54
Possible duplicate of [MySQL / PHP - Find available time slots](https://stackoverflow.com/questions/13414795/mysql-php-find-available-time-slots) — JeffUK, Dec 31 '18 at 16:44
Please provide sample data for each of your tables `Hotel`, `Rooms`, and `Reservation` along with expected results preferably formatted as tabular data in addition to the JSON format you already provided. Also what data types are you using to store `Start`, `End`, `Available_From` and `Available_to`? — Sentinel, Dec 31 '18 at 17:51
@heel I noticed the note about joining with time slots. I believe it is absolutely necessary otherwise you cannot generate 24 rows for whole day. The remaining query could be simplified though. — Salman A, Jan 04 '19 at 23:43
@SalmanA Makes sense. I am thinking the same. How can the rest of the query be simplified? — HeelMega, Jan 05 '19 at 03:22

Sentinel · Accepted Answer · 2018-12-31T20:09:12.937

If you add a Times_Slots table to your data base as shown in this SQL Fiddle:

CREATE TABLE Time_Slots
    (`Slot` time);

INSERT INTO Time_Slots
    (`Slot`)
VALUES
    ('00:00:00'),
    ('01:00:00'),
    ('02:00:00'),
    ('03:00:00'),
    ('04:00:00'),
    ('05:00:00'),
    ('06:00:00'),
    ('07:00:00'),
    ('08:00:00'),
    ('09:00:00'),
    ('10:00:00'),
    ('11:00:00'),
    ('12:00:00'),
    ('13:00:00'),
    ('14:00:00'),
    ('15:00:00'),
    ('16:00:00'),
    ('17:00:00'),
    ('18:00:00'),
    ('19:00:00'),
    ('20:00:00'),
    ('21:00:00'),
    ('22:00:00'),
    ('23:00:00');

Then the following query will provide room availability for all rooms with reservations:

Query 1:

select r.id
     , r.Name
     , res_date + interval t.slot hour_second available
  from Time_Slots t
  join Rooms r
    on t.Slot between r.available_from and r.available_to
  join (select distinct room_id, date(start) res_date from Reservation) res
    on res.room_id = r.id
 where (r.id, res_date + interval t.slot hour_second) not in (
        select r.room_id
             , date(r.start) + interval t.slot hour_second Reserved
          from Time_Slots t
          join Reservation r
            on r.start <= date(r.end) + interval t.slot hour_second
           and date(r.start) + interval t.slot hour_second < r.end)

This query works by first selecting the available slots from Times_Slots for each day that has at least one reservation for that room, and then filtering out the reserved time slots.

Results:

| id |   Name |            available |
|----|--------|----------------------|
|  1 | Single | 2019-01-03T09:00:00Z |
|  1 | Single | 2019-01-03T10:00:00Z |
|  1 | Single | 2019-01-03T11:00:00Z |
|  1 | Single | 2019-01-03T12:00:00Z |
|  1 | Single | 2019-01-03T13:00:00Z |
|  1 | Single | 2019-01-03T14:00:00Z |
|  1 | Single | 2019-01-03T17:00:00Z |
|  1 | Single | 2019-01-03T20:00:00Z |
|  1 | Single | 2019-01-03T21:00:00Z |

In your sample output you indicated that the room was available for 2019-01-03 22:00:00, 2019-01-03 23:00:00, 2019-01-03 24:00:00, however those times are after the Room tables defined availability block, so my query excluded those times.

I'm using an interval expression to do date/time asthmatic since your Reservations store start and end as datetime or timestamp values while your rooms table and my time_slots table store just the times. MySQL didn't like it when I tried adding the time data types to the datetime/timestamp data types, but it was okay if I converted the time data to an interval expression. — Sentinel, Dec 31 '18 at 20:13

SandPiper · Answer 2 · 2018-12-31T19:31:46.733

The first problem you have is your schema setup is poor. You don't have good data normalization. 1) Rename the fields for better clarity. 2) Change these two tables to be like this:

Reservation:
Res_ID | hotel_id | room_id | res_start | res_end | status

Rooms:
Room_ID | hotel_id | room_name | room_number | available_from | available_to

You will need a table that has your time slots defined. You can do it with a CTE and then CROSS JOIN it with your rooms. This is one of the few cases where the CROSS JOIN is useful.

Now do your query like this:

WITH timeslots AS (
    SELECT CURRENT_DATE() AS time_slot UNION
    SELECT CURRENT_DATE() + 1/24   UNION
    SELECT CURRENT_DATE() + 2/24   UNION
    SELECT CURRENT_DATE() + 3/24   UNION
    SELECT CURRENT_DATE() + 4/24   UNION
    SELECT CURRENT_DATE() + 5/24   UNION
    SELECT CURRENT_DATE() + 6/24   UNION
    SELECT CURRENT_DATE() + 7/24   UNION
    SELECT CURRENT_DATE() + 8/24   UNION
    SELECT CURRENT_DATE() + 9/24   UNION
    SELECT CURRENT_DATE() + 10/24  UNION
    SELECT CURRENT_DATE() + 11/24  UNION
    SELECT CURRENT_DATE() + 12/24  UNION
    SELECT CURRENT_DATE() + 13/24  UNION
    SELECT CURRENT_DATE() + 14/24  UNION
    SELECT CURRENT_DATE() + 15/24  UNION
    SELECT CURRENT_DATE() + 16/24  UNION
    SELECT CURRENT_DATE() + 17/24  UNION
    SELECT CURRENT_DATE() + 18/24  UNION
    SELECT CURRENT_DATE() + 19/24  UNION
    SELECT CURRENT_DATE() + 20/24  UNION
    SELECT CURRENT_DATE() + 21/24  UNION
    SELECT CURRENT_DATE() + 22/24  UNION
    SELECT CURRENT_DATE() + 23/24 )
SELECT r.id, r.name, r.number, r.type, r.rating, 
       t.time_slot AS time_slot_open, 
       t.time_slot + 1/24 AS time_slot_close,
       res.Res_ID
FROM rooms r 
     CROSS JOIN timeslots t
     LEFT JOIN reservation res ON res.hotel_id = r.hotel_id AND res.room_id = r.room_id 
         AND time_slot_open >= res.res_start AND time_slot_open < res.res_close

That will get you a list of all your hotel rooms with 24 records each. If there is a reservation in that room, then it will show you the reservation ID for that slot. From here, you can either use the data as is, or you can further put this into its own CTE and just select everything from it where the reservation ID is null. You can also join or look up other data about the reservation based on that ID.

Update

If you run a version of MySQL before 8.0, the WITH clause is not supported (See: How do you use the "WITH" clause in MySQL?). You'll have to make this a subquery like this:

SELECT r.id, r.name, r.number, r.type, r.rating, 
       t.time_slot AS time_slot_open, 
       t.time_slot + 1/24 AS time_slot_close,
       res.Res_ID
FROM rooms r 
     CROSS JOIN (SELECT CURRENT_DATE() AS time_slot UNION
     SELECT CURRENT_DATE() + 1/24   UNION
     SELECT CURRENT_DATE() + 2/24   UNION
     SELECT CURRENT_DATE() + 3/24   UNION
     SELECT CURRENT_DATE() + 4/24   UNION
     SELECT CURRENT_DATE() + 5/24   UNION
     SELECT CURRENT_DATE() + 6/24   UNION
     SELECT CURRENT_DATE() + 7/24   UNION
     SELECT CURRENT_DATE() + 8/24   UNION
     SELECT CURRENT_DATE() + 9/24   UNION
     SELECT CURRENT_DATE() + 10/24  UNION
     SELECT CURRENT_DATE() + 11/24  UNION
     SELECT CURRENT_DATE() + 12/24  UNION
     SELECT CURRENT_DATE() + 13/24  UNION
     SELECT CURRENT_DATE() + 14/24  UNION
     SELECT CURRENT_DATE() + 15/24  UNION
     SELECT CURRENT_DATE() + 16/24  UNION
     SELECT CURRENT_DATE() + 17/24  UNION
     SELECT CURRENT_DATE() + 18/24  UNION
     SELECT CURRENT_DATE() + 19/24  UNION
     SELECT CURRENT_DATE() + 20/24  UNION
     SELECT CURRENT_DATE() + 21/24  UNION
     SELECT CURRENT_DATE() + 22/24  UNION
     SELECT CURRENT_DATE() + 23/24 ) t
     LEFT JOIN reservation res ON res.hotel_id = r.hotel_id AND res.room_id = r.room_id 
         AND time_slot_open >= res.res_start AND time_slot_open < res.res_close

It should be, at least in version 8.0 and up. I know earlier versions didn't have it. See documentation here: https://dev.mysql.com/doc/refman/8.0/en/with.html — SandPiper, Dec 31 '18 at 18:47
Is there an alternative for earlier versions? I am using mysql 5.6 and 5.x is probably the most common version — HeelMega, Dec 31 '18 at 18:48
I had a typo there, I left off the `SELECT` at the beginning of the CTE. If that doesn't work, then it might not be supported. Just make it into a subquery by replacing `CROSS JOIN timeslots t` with `CROSS JOIN (SELECT CURRENT_DATE …) AS t` — SandPiper, Dec 31 '18 at 18:56
Fixed the query! It works... need more medication though in terms of calculation as select current_date() + 1/24 wont get the right calculation — HeelMega, Dec 31 '18 at 19:58
Glad I helped get you to your answer. If you found this content useful, please consider giving it an upvote, even if you don't accept it as the final answer! — SandPiper, Dec 31 '18 at 21:27
I upvoted already. I like this solution better but I am just confusing on how to get the hour minute second value 00:00:00 from your calculations — HeelMega, Dec 31 '18 at 21:30
Nominally you keep datetimes as datetimes until you are ready to display them. In the final `SELECT`, try using `DATE_FORMAT(t.time_slot, %H %i %S)`. Documentation here: https://www.w3schools.com/sql/func_mysql_date_format.asp — SandPiper, Dec 31 '18 at 23:19

score 1 · Answer 3 · answered Jan 03 '19 at 21:58

Plan A (one row per hour)

Get rid of the T and Z; MySQL does not understand that syntax.
Your motel is in a single timezone, correct? Then using either DATETIME or TIMESTAMP is equivalent.
For a 3-hour reservation, make 3 rows. (It is likely to be messier to work with ranges.)
Alas, you are using MySQL, not MariaDB; the latter has automatic sequence generators. Example: The pseudo-table named seq_0_to_23 acts like a table prepopulated with the numbers 0 through 23.
Finding available times requires having a table with all possible hours for all days, hence the note above.
Either do arithmetic or LEFT for hours:
Since LEFT is simple and straightforward,

I will discuss it:

mysql> SELECT NOW(), LEFT(NOW(), 13);
+---------------------+-----------------+
| NOW()               | LEFT(NOW(), 13) |
+---------------------+-----------------+
| 2019-01-03 13:43:56 | 2019-01-03 13   |
+---------------------+-----------------+
1 row in set (0.00 sec)

The second column shows a string that could be used for indicating the 1pm hour on that day.

Plan B (ranges)

Another approach uses ranges. However, the processing is complex since all hours are always associated with either a reservation or with "available". The code gets complex, but the performance is good: http://mysql.rjweb.org/doc.php/ipranges

Plan C (bits)

The table involves a date (no time), plus a MEDIUMINT UNSIGNED which happens to be exactly 24 bits. Each bit represents one hour of the day.

Use various boolean operations:

| (OR) the bits together to see what hours are assigned.
0xFFFFFF & ~hours to see what is available.
BIT_COUNT() to count the bits (hours).
While it is possible in SQL to identify which hours a room is available, it may be better to do that in your client code. I assume you have a PHP/Java/whatever frontend!
etc.

More?

Would you like to discuss any of these in more detail?

T and Z are not there. It is already in date time format. I posted the javascript accidentally in the post. Is there no way to get the available time without the time slot table? — HeelMega, Jan 04 '19 at 01:10
I like the first approach the best. 2nd and 3rd is going to get too complex — HeelMega, Jan 04 '19 at 01:10
@HeelMega - If you follow Plan A, I think you are stuck with the "time slot table" Be aware that you 'need' it 'infinitely far in the future. So some complexity is needed to deal with building it when needed -- as other Answers did. — Rick James, Jan 04 '19 at 01:16

Salman A · Answer 4 · 2019-01-08T07:06:24.180

You need to join the rooms table with a table of timeslots (24 rows). This will generate a list of all possible timeslots for a given room. Filtering out not-available time slots is trivial:

SELECT rooms.id, rooms.name, TIMESTAMP(checkdates.checkdate, timeslots.timeslot) AS datetimeslot
FROM rooms
INNER JOIN (
    SELECT CAST('00:00' AS TIME) AS timeslot UNION
    SELECT CAST('01:00' AS TIME) UNION
    SELECT CAST('02:00' AS TIME) UNION
    SELECT CAST('03:00' AS TIME) UNION
    SELECT CAST('04:00' AS TIME) UNION
    SELECT CAST('05:00' AS TIME) UNION
    SELECT CAST('06:00' AS TIME) UNION
    SELECT CAST('07:00' AS TIME) UNION
    SELECT CAST('08:00' AS TIME) UNION
    SELECT CAST('09:00' AS TIME) UNION
    SELECT CAST('10:00' AS TIME) UNION
    SELECT CAST('11:00' AS TIME) UNION
    SELECT CAST('12:00' AS TIME) UNION
    SELECT CAST('13:00' AS TIME) UNION
    SELECT CAST('14:00' AS TIME) UNION
    SELECT CAST('15:00' AS TIME) UNION
    SELECT CAST('16:00' AS TIME) UNION
    SELECT CAST('17:00' AS TIME) UNION
    SELECT CAST('18:00' AS TIME) UNION
    SELECT CAST('19:00' AS TIME) UNION
    SELECT CAST('20:00' AS TIME) UNION
    SELECT CAST('21:00' AS TIME) UNION
    SELECT CAST('22:00' AS TIME) UNION
    SELECT CAST('23:00' AS TIME)
) AS timeslots ON timeslots.timeslot >= rooms.available_from
              AND timeslots.timeslot <  rooms.available_to
CROSS JOIN (
    SELECT CAST('2019-01-03' AS DATE) AS checkdate
) AS checkdates
WHERE NOT EXISTS (
    SELECT 1
    FROM reservations
    WHERE room_id = rooms.id
    AND TIMESTAMP(checkdates.checkdate, timeslots.timeslot) >= `start`
    AND TIMESTAMP(checkdates.checkdate, timeslots.timeslot) <  `end`
)

Demo on DB Fiddle

The above query checks availability for one date (2019-01-03). For multiple dates simply add them to checkdates.

In your example... 17:00-18:00 is not booked but doesn't show up in the results — HeelMega, Jan 07 '19 at 19:34
@HeelMega If the reservation 15:00—17:00 means the room becomes available on and after 17:00 then replace `<=` with `<`. Similarly if available from/to 09:00—21:00 means room becomes unavailable on and after 21:00 then replace `<=` with `<`. I have updated the answer. — Salman A, Jan 08 '19 at 06:52

Get 1 hour incrementation for room availability MySQL

4 Answers4