CODEWITHSUNDEEP
mysqlsqldatabaserelational-database
HeelMega
HeelMega

Reputation: 508

Get next available time slot after a certain time in mysql

I am trying to get the next available time slot by checking the list of reserved times in the table. Each reservation can be minimam of 1 hour incrementation and gcan go on all day. So for example, if the room is booked from 9 AM to 10 AM, and from 12 AM to 3 PM and I am trying to get the first available slot after 9 am. It will be 10 am.

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:

Reservations

res_id | hotel_id | room_id | res_start | res_end

Rooms:

room_id | hotel_id | room_name | room_number

Here is the query I have so far:

SELECT free_from, free_until
FROM (
  SELECT a.res_end AS free_from,
  (SELECT MIN(c.res_start)
   FROM reservations c
   WHERE c.res_start > a.res_end) as free_until
   and b.res_end > "2019-01-05 11:00:00"
  FROM reservations a
  WHERE NOT EXISTS (
    SELECT 1
    FROM reservations b
    WHERE b.res_start > a.res_end
  )
) as d
ORDER BY free_until-free_from

But I am getting the wrong time returned from it.

Expected result:

room_id | room_name | Next available time
1       | "Single"  | 2019-01-05 10:00:00

Here is a time_slots table

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');

Upvotes: 0

Views: 1074

Answers (2)

Nick
Nick

Reputation: 147166

I think this query will give you the results you want. It looks for any reservation later than the current time whose res_end has no matching res_start for another reservation.

SELECT rm.room_id, rm.room_name, MIN(re.res_end) AS `available from`
FROM reservations re
LEFT JOIN reservations rs ON rs.hotel_id = re.hotel_id AND rs.room_id = re.room_id AND rs.res_start = re.res_end
JOIN rooms rm ON rm.hotel_id = re.hotel_id
WHERE rs.res_id IS NULL AND re.res_end > NOW()
GROUP BY rm.room_id, rm.room_name

Output (for my small demo on dbfiddle):

room_id     room_name   available from
1           Single      2019-01-06 10:00

Update

This query uses a timeslot table to allow for the case where there are no reservations in a room when the time of interest starts:

SELECT rm.room_id, rm.room_name,
       MIN(t.slot) AS `available from`
FROM time_slots t
CROSS JOIN rooms rm
LEFT JOIN reservations r ON HOUR(t.slot) BETWEEN HOUR(r.res_start) AND HOUR(r.res_end) - 1 
                        AND DATE(r.res_start) = DATE(@time)
                        AND r.hotel_id = rm.hotel_id
                        AND r.room_id  = rm.room_id
WHERE t.slot > @time AND r.res_id IS NULL
GROUP BY rm.room_id, rm.room_name
ORDER BY rm.room_id

You would replace @time in this query with your time of interest. I've updated my demo on dbfiddle to demonstrate the query, you can play with adjusting the @time variable to watch it work.

Upvotes: 1

GMB
GMB

Reputation: 222482

You can use a correlated subquery to filter out reservations that are immediately followed by another one ; the aggregated outer query returns the earlier available timeslot for each room.

SELECT 
    roo.hotel_id,
    roo.room_id,
    roo.room_name,
    MIN(res.res_end) next_available_time
FROM 
    reservations res
    INNER JOINS rooms roo 
        ON  roo.hotel_id = res.hotel_id 
        AND roo.room_id  = res.room_id
WHERE 
    res.res_end > "2019-01-05 11:00:00"
    AND NOT EXISTS (
        SELECT 1 
        FROM reservations
        WHERE 
            res_start = res.res_end 
            AND hotel_id = res.hotel_id 
            AND room_id  = res.room_id
    ) 
GROUP BY 
    res.hotel_id,
    res.room_id,
    roo.room_name

Tested in this DB fiddle.

I assumed that both columns hotel_id and room_id where to be used when joining tables (if room_id was enough, that would shorten the query a little).

Upvotes: 1

Related Questions