Postgres overlapping dates query - see if two dates as a range overlap a month

Question

I am doing a query to see if a date range [start_date] - [end_date] overlap a month.

So far I have:

select * from my table
where (start_date, end_date) overlaps ('2000-02-01', '2000-02-28')

I need it to contain date ranges that start outside of the month but go into the month, or start in the month and go into the next month: e.g.

'2000-01-31', '2000-02-01' '2000-02-28', '2000-03-01'

however these are not being included in the query result.

It would also be great if I could just put the datepart of the month instead of overlaps ('2000-02-01', '2000-02-28')

Any help appreciated

Answer 1

You can use a daterange:

select * 
from the_table
where daterange(start_date, end_date, '[]') && daterange(date '2000-02-01', date '2000-02-28', '[]')

The parameter '[]' creates an "inclusive" range. This will also work properly for partial or multiple months (and can even be indexed efficiently)

Answer 2

I would just be explicit:

select t.*
from mytable t
where (start_date < '2000-02-01' and end_date >= '2000-02-01')
      (end_date  > '2000-02-28' and start_date <= '2000-02-28'