Query to get vacant time MYSQL

Question

Would like some logical help on formulating a MYSQL Query that gets results that isn't within the data of the table.

I have a table named schedule that has columns with data type 'time' that indicates when this certain schedule starts and ends and a foreign key referencing from table 'rooms' in which the schedule will take place. And in the php code in its search feature, I wanted to add a feature that shows results of rooms that are currently not being occupied by a schedule or is vacant. I added a jquery slider to specifically fetch the start time and end time the searcher wanted.

   TABLE 'schedule'
   room        sched_start      sched_end
     1           09:00:00       10:00:00
     1           11:00:00       12:00:00
     2           07:30:00       08:30:00
     2           11:30:00       13:00:00

For example, the searcher wanted to search a vacant room from 10:00:00 to 11:00:00. Basing from the database, the result should show that both rooms, room 1 and room 2, should be displayed in the search result as both rooms won't be occupied within the specified time of the searcher. I was thinking of comparing chronologically the schedule of all the similar rooms, the 'sched_end' of the first row or the first schedule and the sched_start of the succeeding row or the schedule and so on, so to determine whether there is a vacant time in between. Can anyone help me on this?

All helps and hates would be very much appreciated as I can be as much noob in MySQL-ing.

Answer 1

DROP TABLE IF EXISTS schedule;

CREATE TABLE schedule
(room        INT NOT NULL
,schedule_start      TIME NOT NULL
,schedule_end TIME NOT NULL
,PRIMARY KEY(room,schedule_start)
);

INSERT INTO schedule VALUES
(1,'09:00:00','10:00:00'),
(1,'11:00:00','12:00:00'),
(2,'07:30:00','08:30:00'),
(2,'11:30:00','13:00:00'),
(3,'09:30:00','10:30:00'),
(3,'11:00:00','12:00:00'),
(4,'10:30:00','10:45:00');

SET @start:= '10:00:00';
SET @end:= '11:00:00';

SELECT DISTINCT x.room
        -- or whatever columns you want from whichever table you want 
           FROM schedule x 
           LEFT 
           JOIN schedule y 
             ON y.room = x.room 
            AND y.schedule_start < @end 
            AND y.schedule_end > @start 
        -- other tables can join in here
          WHERE y.room IS NULL;
+------+
| room |
+------+
|    1 |
|    2 |
+------+

http://sqlfiddle.com/#!9/1b677/1

Just to demonstrate that @M0rtiis's solution is wrong...

SELECT DISTINCT room
           FROM schedule
          WHERE @end <= schedule_start
             OR @start >= schedule_end;
+------+
| room |
+------+
|    1 |
|    2 |
|    3 |
+------+

Answer 2

What you need is to specifically exclude the rooms that are occupied in the given period.

SET @start = '10:00:01';
SET @end = '10:59:59';

SELECT *
FROM `schedule` -- you probably want to select from rooms here...
WHERE room NOT IN (
  SELECT room
  FROM `schedule`
  WHERE sched_start BETWEEN @start AND @end
  OR sched_end BETWEEN @start AND @end
  OR @start BETWEEN sched_start AND sched_end
  OR @end BETWEEN sched_start AND sched_end
);

Note that I compensated the "start inclusive" behaviour by adding one second to the start time and subtracting one second from the end time. You should do that before you feed the times to SQL, to avoid those calculations there.

This query filters all cases, including overlapping meetings.

Or, perhaps slightly more coherently:

SET @start:= '10:00:00';
SET @end:= '11:00:00';

SELECT DISTINCT room
           FROM schedule
          WHERE room NOT IN ( SELECT room
                                FROM schedule
                               WHERE schedule_start < @end
                                 AND schedule_end > @start );

Also, you really need proper indexes if this query is to perform with more than just a couple of rows. Use the EXPLAIN function to help you.

Answer 3

Its bad idea to store there TIME. use DATETIME instead to cover cases where need_start - one day and need_end - another (next? or i want to be guest in your hotel for a week?) day.

But anyway, on what u have now try this

SELECT DISTINCT 
  room
FROM schedule
WHERE 
    '11:00:00' <= sched_start
    OR
    '10:00:00' >= sched_end

http://sqlfiddle.com/#!9/dafae/9

Answer 4

You can use BETWEEN operator.

SELECT *
FROM schedule
WHERE sched_end BETWEEN '10:00:00' AND '11:00:00'