SQL / PHP: obtenga todos los resultados dentro del tiempo X a Y, y detecte si hay momentos disponibles entremedio

Lo siento si el título es un poco vago ..

Tengo una base de datos parecida a esta:

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

Ejemplo de datos:

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

Estoy buscando, por ejemplo, una fecha determinada y, obviamente, recibiendo todas las reservas ese día. Como puede ver en los datos de ejemplo, la sala está disponible entre las 10:00:00 y las 13:00:00. ¿Cómo puedo atrapar esto?

Estaba pensando en recorrer el tiempo 07: 00: 00-16: 00: 00 (con una consulta para cada uno) y verificar si obtengo algún resultado de sql. Si obtengo resultados, sabré que la sala está ocupada, pero como hay incógnitas aquí (por ejemplo, 08:00:00 y 09:00:00 no existe), recibiré falsos positivos en esto.

¿Algún consejo aquí?

Una manera sería usar una “tabla de calendario”, o si solo le interesa algún día, una “tabla de reloj” funcionaría. Lo siguiente ilustra (aproximadamente) cómo lo usarías.

 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 es un ejemplo de cómo crear un calendario en MySQL

Con esta información:

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

para obtener el intervalo / intervalo de tiempo disponible, puede emitir esta consulta:

 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`) 

la consulta anterior volverá:

 10:00:00 13:00:00 

Aquí está el violín de SQL: http://sqlfiddle.com/#!2/3c124/25

Nota: la consulta anterior fue amablemente adaptada de esta respuesta por @fthiella :

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


Con esta consulta adicional:

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

Devolverá 1 , es decir, no hay reserva entre la hora de inicio ( from ) y la hora de finalización ( to ), por lo que la sala está disponible.

Aquí está el SQL Fiddle para jugar con los datos: http://sqlfiddle.com/#!2/3c124/1