Postgres Pro
Downloads
Home   >   mailing lists

Thread: Date interval overlaps

Date interval overlaps

From
Ruzsinszky Attila
Date:
Hi,

My test query:

select (date '2009-08-23', date '2009-08-31') overlaps (date
'2009-08-31', date '2009-08-31');

I want testing whether a given date is in an interval.
Why did I get false?


TIA,
Ruzsi

Re: Date interval overlaps

From
Tom Lane
Date:
Ruzsinszky Attila <ruzsinszky.attila@gmail.com> writes:
> select (date '2009-08-23', date '2009-08-31') overlaps (date
> '2009-08-31', date '2009-08-31');

> I want testing whether a given date is in an interval.
> Why did I get false?

The SQL spec says that an exact match on ending endpoint isn't an overlap.
In particular, SQL92 defines the result of OVERLAPS as

              ( S1 > S2 AND NOT ( S1 >= T2 AND T1 >= T2 ) )
              OR
              ( S2 > S1 AND NOT ( S2 >= T1 AND T2 >= T1 ) )
              OR
              ( S1 = S2 AND ( T1 <> T2 OR T1 = T2 ) )

where S1,T1 are the smaller and larger endpoints of the first interval
and similarly for S2,T2.  You've got S2 > S1, so the middle line
applies, but S2 = T1 and T2 = T1 so the result is false.

            regards, tom lane

Re: Date interval overlaps

From
Ruzsinszky Attila
Date:
Hi,

Thanks for the explanation.

Any simple solution for my task? (in SQL)

TIA,
Ruzsi

Re: Date interval overlaps

From
Ruzsinszky Attila
Date:
Hi,

Related question:

date '2009-07-22' - date '2009-07-17' -> 5.

If I count on my fingers, I got 6. (17, 18, 19, 20, 21, 22)
I know I can add +1 and everything is all right.

TIA,
Ruzsi

Re: Date interval overlaps

From
"A. Kretschmer"
Date:
In response to Ruzsinszky Attila :
> Hi,
>
> Related question:
>
> date '2009-07-22' - date '2009-07-17' -> 5.
>
> If I count on my fingers, I got 6. (17, 18, 19, 20, 21, 22)

22 - 17 != 6


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net