Check for intersection between 2 dateranges in Postgres

user_18567855 0
Viewed 62

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'
postgresql sql
1 Answers

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
user_19957380
Related