SQL / PHP: get all results within time X to Y, and detect if there are available times inbetween

Question

Sorry if the title is a bit vague..

I have a database looking like this:

orderid | roomname | date(DATE) | from(TIME) | to(TIME)

Example-data:

1231 | E12 | 2013-04-05 | 07:00:00 | 10:00:00
1671 | E12 | 2013-04-05 | 13:00:00 | 14:00:00

I'm for example searching up a certain date and obviously getting all reservations on that day. As you can see on the example-data, the room is available between 10:00:00 and 13:00:00. How can I catch this?

I was thinking about looping through time 07:00:00-16:00:00 (with one query for each) and check if I get any results from sql. If I do get results, I will know that the room is busy, but since there are unknowns here (ex. 08:00:00 and 09:00:00 doesn't exists), I will get false-positives on this.

Any tips here?

Answer 1

One way would be using a 'calendar table', or if you're only ever interested in one day a 'clock table' would do. The following illustrates (roughly) how you'd use it.

SELECT clock.time AS available
FROM clock
LEFT JOIN bookings ON clock.time BETWEEN bookings.from AND bookings.to
                  AND bookings.date = '2013-01-01' 
WHERE bookings.id IS NULL

http://www.brianshowalter.com/calendar_tables is an example of how to create a calendar in MySQL

Answer 2

With this data:

create table rooms
(
  orderid int not null,
  roomname varchar(8) not null,
  date date not null,
  `from` time not null,
  `to` time not null
);

insert into rooms values (1231, 'E12', '2013-04-05', '07:00', '10:00');
insert into rooms values (1671, 'E12', '2013-04-05', '13:00', '14:00');

to get the available time interval/slot, you can issue this query:

SELECT DATE_FORMAT(r1.`to`, '%T') AS `From`, DATE_FORMAT(min(r2.`from`), '%T') AS `To`
FROM
  rooms r1 JOIN rooms r2
  ON r1.`to`< r2.`from`
WHERE r1.date = '2013-04-05' AND r1.roomname = 'E12'
GROUP BY r1.`to`
HAVING
  NOT EXISTS (SELECT NULL FROM rooms r3
              WHERE r1.`to` < r3.`to`
              AND min(r2.`from`) > r3.`from`)

the above query will return:

10:00:00    13:00:00

Here's the SQL fiddle: http://sqlfiddle.com/#!2/3c124/25

Note: the above query was kindly adapted from this answer by @fthiella:

https://stackoverflow.com/a/14139835/114029

---

With this additional query:

SELECT (COUNT(*) = 0) AS Available
    FROM rooms
    WHERE roomname = 'E12' AND date = '2013-04-05' AND
    (
        (`from` < MAKETIME(10,00,00) AND `to` > MAKETIME(10,00,00)) OR
        (`from` < MAKETIME(13,00,00) AND `to` > MAKETIME(13,00,00))
    )   

It'll return 1, that is, there's no reservation between the given start time (from) and end time (to) and so the room is available.

Here's the SQL Fiddle to play with the data: http://sqlfiddle.com/#!2/3c124/1