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?

CodePudding user response：

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;

CodePudding user response：

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: