Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Query to get vacant time MYSQL [duplicate]

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.

like image 884
zangetsu Avatar asked Aug 15 '15 07:08

zangetsu


People also ask

How do I get the current time in MySQL?

MySQL provides several useful temporal functions for manipulating TIME data. To get the current time of the database server, you use the CURRENT_TIME function. The CURRENT_TIME function returns the current time value as a string ( 'HH:MM:SS') or a numeric value ( HHMMSS) depending on the context where the function is used.

What is the use of time function in MySQL?

MySQL TIME () Function 1 Definition and Usage. The TIME () function extracts the time part from a given time/datetime. ... 2 Syntax 3 Parameter Values 4 Technical Details. From MySQL 4.0 5 More Examples

How do I measure query execution time in MySQL?

There are two ways to measure query execution time in MySQL – for single queries and for multiple queries. When you run SQL queries in MySQL console, it shows the query execution time after the result is displayed. But you won’t get this information if you are running queries on a server-side program.

How to log query execution time in SQL Server?

If you want to log only those queries whose execution time exceeds 5 seconds, then set long_query_time to 5. Save and close the file. Now whenever a query takes more than 5 seconds to run, its details will be saved to log file. As your log file accumulates query execution time, it will become difficult to manually go through all this information.


2 Answers

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 |
+------+
like image 70
Strawberry Avatar answered Oct 15 '22 21:10

Strawberry


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.

like image 34
Arjan Avatar answered Oct 15 '22 22:10

Arjan