SQL OVERLAPS operator problem, how to get rid of it

Question

I expect that date period from '2011-01-28' to '2011-02-01' OVERLAPS period from '2011-02-01' to '2011-02-01' (that's the same day here), but it does not!

PostgreSQL expecting the match of exact ending point is NOT a match...how to get rid of this? I would like to have it treat the above scenario as overlap.

SELECT (DATE '2011-01-28', DATE '2011-02-01') OVERLAPS
       (DATE '2011-02-01', DATE '2011-02-01');

returns false, while I expect it to return true.

Answer 1

You expect wrong. From the fine manual:

Each time period is considered to represent the half-open interval start <= time < end, unless start and end are equal in which case it represents that single time instant. This means for instance that two time periods with only an endpoint in common do not overlap.

So, if you want the closed interval, start <= time <= end, then you can either do end-point checks explicitly as Catcall suggests or you can add a single day to the upper bound:

SELECT (DATE '2011-01-28', DATE '2011-02-01' + 1) OVERLAPS
       (DATE '2011-02-01', DATE '2011-02-01'    )

But be careful to put the end-points in the correct order as:

When a pair of values is provided, either the start or the end can be written first; OVERLAPS automatically takes the earlier value of the pair as the start.

Answer 2

I think the simplest way is to use the overlaps operator as is, and add to the WHERE clause to treat adjoining dates specially.

Assuming

• (S1, E1) are the start and end dates of the first range,
• (S2, E2) are the start and end dates of the second range,
• none of S1, E1, S2, or E2 are NULL,

then this should return a row when the ranges either overlap or adjoin each other.

select S1, E1, S2, E2
from [some table name]
where (S1, E1) overlaps (S2, E2)
   or (E1 = S2)   -- first range ends on the start date of the second
   or (E2 = S1)   -- second range ends on the start date of the first