sqlpostgresqlaggregate-functions

Check for intersection between 2 dateranges in Postgres

So the idea is very simple :

I have a table containing a start_date and end_date columns .

I also have 2 parameters (initial_date, final_date).

I want to check of my daterange [initital_date, final_date] has an intersection within any possible combination of the [start_date, end_date] daterange.

for example, if I have this following table :

id | start_date | end_date 
1   '2022-05-23'  '2022-05-26'
2   '2022-05-25'  '2022-05-30'
3   '2022-05-01'  '2022-05-20'

and if initial_date = 2022-05-19 and final_date = 2022-05-24

my desired output would be:

id | start_date | end_date 
1   '2022-05-23'  '2022-05-26'
3   '2022-05-01'  '2022-05-20'
Solution

  • Use the daterange overlap operator &&:

    select *
from my_table
where daterange(start_date, end_date) && daterange('2022-05-19', '2022-05-24')

    or the standard-compliant overlaps:

    select *
from my_table
where (start_date, end_date) overlaps ('2022-05-19', '2022-05-24')

    Test it in db<>fiddle.

    Note. Both of the above solutions assume that each time period represents the half-open interval start <= time < end. In the first variant, you can close the end of the range as follows:

    daterange(start_date, end_date, '[]') && daterange('2022-05-19', '2022-05-23', '[]'),

    Read in the docs:

    is it possible to return a true/false boolean if a match is found instead of returning rows?

    You can do that in an elegant way with a Postgres Boolean aggregate function:

    select bool_or((start_date, end_date) overlaps ('2022-05-19', '2022-05-24'))
from my_table