Query to get vacant time MYSQL [duplicate]

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.

Higgs

so the user wants either those rows that satisfy or do not satisfy the following condition: input start < table end and input end > table start. NOT EXISTS or LEFT JOIN ... WHERE... IS NULL can help with the negative condition – Cerate

24 hours format? where is the DATE? – Lives

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

Cerate

so why you use join by null instead of invert the conditions?) – Lives

not really. if we assume that input (@start, @end) is correct, "start" is before "end" (otherwise no sence at all) "any instance that" is actually the same we need. Time is linear? – Lives

in this certain case they are the same. more complexity u added – Lives

Yes. u are right. great example – Lives

Yes I think this actually worked perfectly. You see I have not been very clear with my question. Another column that is in the schedule table are the days that indicate when the schedule takes place in a week but this is perfect. – Higgs

If I can just double and triple vote up your answer cause it's just perfect – Higgs

@carlowmanangan I hope that days column is a bitwise integer where 1 represents 'Monday', say, and 127 represents 'every day' !!! – Cerate

@Cerate Actually that isn't how I did it but I will do try modifying my table now – Higgs

@Cerate By the way I have another problem if it's still ok because I really don't know where to start now although I have now on hand your answer as the best-est solution. You see I wanted to display the row values of the room_id since it's a foreign referencing to the table room. Instead of plainly displaying just the room_id from table schedule, I wanted the values within that foreign key to be displayed. Other columns inside the table room include room_description, room_subdescription, and room_bldg If you can still help me out on this I'd be so much more than grateful. – Higgs

@Cerate right thanks :) my question is up there by the way :D – Higgs

@carlomanangan that's just a simple join. I'm confident that you can figure that bit out for yourself – Cerate

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.

Prescribe

That's very comprehensive ! – Cysto

no no :) 10:00:00 IS between 10:00:00 and any other time. u should play with "+/-1 second" then – Lives

Time is linear. In consequence, your criteria are unnecessarily complex. – Cerate

@M0rtiis: you (most likely) want to be able to plan meetings back to back, so you should be able to start a meeting at 10:00:00 if the previous one ends at that exact time. That gives issues with BETWEEN, because between is inclusive on the edges. – Prescribe

@Strawberry: There are some edge cases to keep in mind. If you want to plan a meeting from 10:00 to 11:00, you should not be able to book the room that's already booked from 10:15 to 10:45, nor the room that's been booked from 9:00 to 12:00. – Prescribe

@Prescribe No, that's not right. See my answer below (or perhaps above ;-) ). I find it helps to draw a diagram. – Cerate

That does seem to work too :) – Prescribe

If you apply the changes I mentioned in my answer (+1 second for start time, -1 second for end time) you do get the expected results. – Prescribe

Sure: sqlfiddle.com/#!9/1b677/3 – Prescribe

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

Lives

Actually its a bad idea to use Datetime !!! You need to store date and time separately. Otherwise as an example, finding something free at 10am any day becomes awful. – Cysto

Really? if we ll rely here not on TIME but also on DATE. We ll use here a couple of "NOT BETWEEN". What is much simplier to do on DATETIME rather concatenation of DATE & TIME yea? BTW "finding something free at 10am any day" should be "finding something free from datetime1 to datetime2". are u joking? – Lives

Answer 4

You can use BETWEEN operator.

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

Rolfston

How is this going to give him what he wants ? – Cysto

The question was how to compare schedule if 2 inputs are given. The query definitely gave him an idea on how to achieve the requirement. – Rolfston

@RohitGupta The question was tagged with PHP, so actually this solution could be used in conjunction with application level logic to arrive a the answer - but it's not the way I'd do it. – Cerate