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.
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.
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
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.
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.
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 |
+------+
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With