CODEWITHSUNDEEP
sqlpostgresql
Mellon
Mellon

Reputation: 38872

SQL OVERLAPS operator problem, how to get rid of it

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.

Upvotes: 8

Views: 23276

Answers (5)

Holger Jakobs
Holger Jakobs

Reputation: 1062

Why not use the datatype daterange of PostgreSQL? Using this type, you can choose whether you want the bound inclusive or exclusive.

SELECT '[2011-01-28, 2011-02-01]'::daterange &&
       '[2011-02-01, 2011-02-01]'::daterange;

The && operator tells you whether the two values overlap or not.

PostgreSQL documentation on range types

Upvotes: 2

Máťa - Stitod.cz
Máťa - Stitod.cz

Reputation: 893

Why not use classic query:

select (date '2013-01-01' between date '2012-12-01' and date '2013-01-01') or
       (date '2013-03-01' between date '2012-12-01' and date '2013-01-01');

In pseudocode:

select (date 'START1' between date 'START2' and date 'END2') or
       (date 'END1' between date 'START2' and date 'END2');

Result will be same though order will be changed:

select (date 'START2' between date 'START1' and date 'END1') or
       (date 'END2' between date 'START1' and date 'END1');

Upvotes: 0

mu is too short
mu is too short

Reputation: 434735

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.

Upvotes: 17

Mike Sherrill &#39;Cat Recall&#39;
Mike Sherrill &#39;Cat Recall&#39;

Reputation: 95642

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

Upvotes: 10

Milen A. Radev
Milen A. Radev

Reputation: 62613

A kind of a workaround, i.e. it may or may not make sense for your case - convert the dates to timestamps:

SELECT (TIMESTAMP '2011-01-28 00:00:00', TIMESTAMP '2011-02-01  23:59:59') OVERLAPS (TIMESTAMP '2011-02-01 00:00:00', TIMESTAMP '2011-02-01 23:59:59');

Technically it's sufficient to convert only the endpoint of the first period, at least for the example you've given.

Upvotes: 4

Related Questions