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

I think you need to avoid the use of the actual begin and end dates of a month because they vary too much, up-voted your question because this actually made my brain crack and I'm curious for the result :) — Nebulosar
– Nebulosar, Commented Jun 5, 2018 at 13:15
In Standard SQL overlaps works on periods where the start is included, but the end is excluded (you could adjust that by adding a day to the end). What about ranges when they start before that month and end after it? Or ranges within that month? — dnoeth
– dnoeth, Commented Jun 5, 2018 at 13:16

Bohemian · Accepted Answer · 2018-06-05 13:25:54Z

9

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)

edited Jun 5, 2018 at 13:25

Bohemian♦

427k103 gold badges604 silver badges750 bronze badges

answered Jun 5, 2018 at 13:19

user330315

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2018-06-06 01:27:46Z

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'

edited Jun 6, 2018 at 1:27

answered Jun 5, 2018 at 13:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

Nebulosar Over a year ago

This only works for one month, I know it's the example, but what would be a more abstract way to tackle this problem? Is there a way, or does every month need to be hard-coded? What about leap-years?

dnoeth Over a year ago

If you use Standard SQL's inclusive-exclusive periods there's no need to know about the end of a month, e.g. February 2000 is from '2000-02-01' to '2000-03-01' :-)

Gordon Linoff Over a year ago

@Nebulosar . . . I don't get your point at all. This is tied to two dates, not to months. You simply need to plug in the dates.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related