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.
0 Answer
Looking at the entry in your database and the query you're using, it seems like you're actually looking for a date interval 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
这家伙很懒，什么都没留下...