Checking if a time falls within a time range in MySQL

Question

I know this can be accomplished on the backend - but I'm wondering if there's any native, or efficient MySQL function that can be used to check if a given time variable falls within a range covered by two time variables (a start_time, and an end_time).

I have a database currently set up which looks like the following;

+----+--------+------------+----------+
| id | job_id | start_time | end_time |
+----+--------+------------+----------+
|  1 |     40 | 13:00:00   | 14:00:00 |
|  2 |     44 | 14:45:00   | 15:00:00 |
|  3 |     45 | 15:10:00   | 15:30:00 |
+----+--------+------------+----------+

The backend accepts a start_time, and an end_time with a job_id - and then it looks to see if it can be fit in anywhere. So for example, given a start_time of 13:30:00, and an end_time of 13:45:00, the backend should reject this job request as there is no time available for it (it would overlap with the entry at id 1.)

However, if a job is submitted with a start_time of 14:10:00, and an end time of 14:20:00, it should be accepted - as this does not overlap with any existing tasks.

The following query is great to tell if a job can be submitted for say, 13:00:00 until 14:00:00 (an exact duplicate of id 1);

SELECT * WHERE start_time >= '13:00:00' AND end_time <= '14:00:00';

But if the start_time becomes 13:01:00, then the query falls down - as the start_time is less than 13:01:00, at 13:00:00. So it'll get approved for insertion, as the above query will return no overlapping results.

If we change the query to an OR clause on end_time, then literally any job that doesn't end before 14:00:00 would be rejected.

Can anyone suggest a simple way of taking an input variable of a time type, and checking if it falls within range of all available start_time, and end_time variables as noted in the db above?

`SELECT * WHERE start_time <= '14:00:00' AND end_time >= '13:00:00';` - But you probably want `<` and `>` instead of `<=` and `>=`. — Paul Spiegel, Jun 05 '18 at 16:15
@lurker Yes, if any project ends before it starts, then we're all in trouble. — Strawberry, Jun 05 '18 at 16:40
@Strawberry Though "I need it by yesterday" is quite common. — Paul Spiegel, Jun 05 '18 at 16:49
@Strawberry It depends how you use it, if it's for you terrible, doesn't mean it's useful. You just haven't used it in right way. — slon, Jun 05 '18 at 17:11
@slon Or maybe it's just not terribly useful in this instance. — Strawberry, Jun 05 '18 at 17:12

slon · Answer 1 · 2018-06-05T16:52:24.580

I would suggest you to check in in one of the following ways: hours/minutes/seconds

SELECT *  FROM timetableTemp 
WHERE HOUR(TIMEDIFF(start_time, end_time))>=1 
AND start_time >=start_time

OR use BETWEEN function

SELECT *  FROM timetableTemp 
WHERE '13:00:00' BETWEEN start_time AND end_time

support:

SELECT SECOND(TIMEDIFF("13:10:11", "13:10:10")) AS Seconds; -- 1
SELECT MINUTE(TIMEDIFF("13:10:11", "13:10:10")) AS Minutes; -- 0
SELECT HOUR(TIMEDIFF("13:00:00", "14:00:00")) AS Hours; -- 1

score 0 · Answer 2 · answered Jun 05 '18 at 17:03

Let's suppose you have a new job with start of new_start and end of new_end. Assume your existing list is in order of time of each job entry, and they are all non-overlapping.

What you'd need to check to see if the new job fits in the list without overlap would be:

new_end is less than the start_time of the first list entry, in which case the new entry fits at the beginning, OR
new_start is greater than the end_time of the last list entry, in which case the new entry fits at the end, OR
For some in-between list entry, new_start is greater than the end_time and new_end is less than the start_time of the following list entry, in which case the new entry fits in between this entry and the next.

Checking if a time falls within a time range in MySQL

2 Answers2