How to match all rows in a table in which an interval intersects the `start_time` and `end_time` columns?
Solution 1:
Postgres knows range data types (which you could use in your table) and also a range overlap operator &&.
SELECT *
FROM elbat
WHERE tstzrange(start_time, end_time)
&& '[2022-01-23 16:23:00.000+00, 2022-01-23 18:44:00.000+00]'::tstzrange;
Solution 2:
You have to use this:
SELECT * FROM your_table
WHERE start_time < '2022-01-23 18:44:00.000+00' AND end_time > '2022-01-23 16:23:00.000+00'
So start_time has to be less than the end of the search interval and end_time has to be greater than the start of the interval.
DB Fiddle
Output:
| id | start_time | end_time |
|---|---|---|
| 2 | 2022-01-23T16:00:00.000Z | 2022-01-23T17:00:00.000Z |
| 3 | 2022-01-23T17:00:00.000Z | 2022-01-23T18:00:00.000Z |
| 4 | 2022-01-23T18:00:00.000Z | 2022-01-23T19:00:00.000Z |