postgres query for overlapping integer time intervals-CodePudding

I have a postgres table of the form

from_secs, to_secs, value
       10       20      1
       12       50      2
       
              ....

now at query time I get a list of time intervals [(from_secs1, to_secs1), (from_secs2, to_secs2), ...] and I need to get all the values which (from_secs, to_secs) overlap with at least 1 of the interval in the list.

How can I do that?

EXAMPLE:

Taking as example the above table and an input list of [(1, 11), (55, 100)]

Then the query should return the first row of the table as it is the only one that overlap with at least 1 interval of the list

CodePudding user response：

You can use an integer range built from the from_secs, to_secs combination and compare that to a multirange.

Then use the overlaps operator

select *
from the_table
where int4range(from_secs, to_secs, '[]') && int4multirange(int4range(1, 11,'[]'), int4range(55, 100,'[]'));

The ranges are all created as "inclusive" on both edges.