Select where datetime start greater than and end less than

Question

I have a table with start and end field with values such as:

start               | end
2021-09-24 17:00:00 | 2021-10-01 08:00:00

I have gone foggy headed and can't figure out why this statement results in nothing:

SELECT *
FROM `oncall`
WHERE `start` >= '2021-10-01 02:00:00'
AND   `end`   <= '2021-10-01 08:00:00'

although the date range in my example row contains the above range.

I am trying to find any row that overlaps the start and end of the values in the database.

Answer 1

Looking at the entry in your database and the query you're using, it seems like you're actually looking for a date interval overlap query i.e. a query that checks if [2021-10-01 02:00:00, 2021-10-01 08:00:00) overlaps [2021-09-24 17:00:00, 2021-10-01 08:00:00) somehow.

SELECT *
FROM `oncall`
WHERE @d2 > `start` AND `end` > @d1

-- replace @d1 and @d2 with actual values

Note that this query works for all kinds of overlap. For example if you have this pair of dates in your database:

2021-10-11 | 2021-10-15

then all of these input pairs will match:

2021-10-01 | 2021-10-12
2021-10-14 | 2021-10-20
2021-10-01 | 2021-10-20
2021-10-11 | 2021-10-14