How to match all rows in a table in which an interval intersects the `start_time` and `end_time` columns?

Question

I have table with start_time and end_time colums both set to timestamp with timezone type. This table contains consecutive intervals, i.e. end_time of one row is the start_time of the following one.

Now, I need to select all rows that intersect a specific interval (two timestamps).

For example, a table could look like this:

 id |         start_time         |          end_time
----+----------------------------+----------------------------
 1  | 2022-01-23 15:00:00.000+00 | 2022-01-23 16:00:00.000+00
 2  | 2022-01-23 16:00:00.000+00 | 2022-01-23 17:00:00.000+00
 3  | 2022-01-23 17:00:00.000+00 | 2022-01-23 18:00:00.000+00
 4  | 2022-01-23 18:00:00.000+00 | 2022-01-23 19:00:00.000+00

I’d like to select all row which intersect with the interval starting at 2022-01-23 16:23:00.000+00 and ending at 2022-01-23 18:44:00.000+00, i.e. row IDs 2-4.

I can do it make it using three SQL commands:

• get the ID of row in where the first timestamp is;
• get the ID of row in where the second timestamp is;
• select all rows based on the previous IDs.

However, neither of the search times might be included in any of the intervals in the table (like any timestamp before 2022-01-23 15:00:00.000+00 or after 2022-01-23 19:00:00.000+00).

Is there a better way?

Answer 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;