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'

CodePudding user response：

Use the overlap operator &&:

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

or 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 is considered to represent 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:

• Range/Multirange Functions and Operators.
• Date/Time Functions and Operators.

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