Geek asks and answers

OVERLAPS SQL statement problem, how to get rid of it

I expect this period from '2011-01-28' to '2011-02-01' OVERLAPS the period from '2011-02-01' to '2011-02-01' (which is on the same day here), but this is not true!

PostgreSQL, expecting an exact endpoint to match, is NOT a match ... how do I get rid of this? I would like him to consider the above scenario as an 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.

+3
source share
4 answers

As a rule, this may or may not make sense for your case - convert 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 enough to convert only the endpoint of the first period, at least for the example you specified.

+2

, . :

<= time < , , . , , .

, , start <= time <= end, , Catcall, :

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

, , :

, , ; OVERLAPS .

+9

, - WHERE, .

  • (S1, E1) - ,
  • (S2, E2) - ,
  • S1, E1, S2 E2 NULL,

, , .

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
+5

:

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');

:

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

, :

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

More articles:


All Articles