mysql select time interval that does not fall under a given interval

Question

Suppose I have the following table

+----------+------------+-----------+----------+
| class_id | teacher_id | starts_at | ends_at  |
+----------+------------+-----------+----------+
|       39 |          5 | 15:00:00  | 21:00:00 |
|       40 |         20 | 18:00:00  | 21:00:00 |
|       41 |         59 | 18:00:00  | 21:00:00 |
|       42 |         21 | 18:30:00  | 20:00:00 |
|       43 |         80 | 18:30:00  | 21:00:00 |
|       44 |       NULL | 17:30:00  | 21:00:00 |
|       45 |        140 | 17:30:00  | 20:00:00 |
|       46 |        123 | 18:30:00  | 21:00:00 |
+----------+------------+-----------+----------+

For a particular instance, I do not have any teacher available in hand but I need to select those teacher whose class timings do not fall under a certain given time. For example. A new class (class_id=47) has been announced with a timing of 15:00:00 to 17:30:00. Now I want to select the teacher_id's, who's existing class timings do not fall under this interval. How should I write this query?

Tried this but I was wrong:

SELECT * from classes 
where time(starts_at) not between '15:00:00' and '17:30:00' 
and time(ends_at) not between '15:00:00' and '17:30:00'

Answer 1

This is just the overlapping range problem. You may use the following logic:

SELECT *
FROM classes 
WHERE '15:00:00' >= ends_at OR '17:30:00' <= starts_at;

Demo

The logic I used is borrowed from the canonical question Check overlap of date ranges in MySQL

To find records which overlap with a new incoming start and end time, we would use:

WHERE new_start < existing_end AND new_end > existing_start;

However, in your case, you want ranges which have zero overlap, i.e. the opposite of the above. By DeMorgan's laws, the above logic then becomes:

WHERE new_start >= existing_end AND new_end <= existing_start;