Thread: overlaps() does not work as expected?

I've quite some trouble with the overlaps function:
SELECT overlaps('9.6.2005'::date, '9.6.2005'::date, '9.6.2005'::date, 
'9.6.2005'::date);
returns true (these are german timestamps dd.mm.yyyy)

SELECT overlaps('8.6.2005'::date, '9.6.2005'::date, '9.6.2005'::date, 
'9.6.2005'::date); 
returns false

Is this a bug or a (quite strange) feature?

Thanks!

On Fri, May 27, 2005 at 06:35:32PM +0200, Mario Weilguni wrote:
> I've quite some trouble with the overlaps function:
> SELECT overlaps('9.6.2005'::date, '9.6.2005'::date, '9.6.2005'::date, 
> '9.6.2005'::date);
> returns true (these are german timestamps dd.mm.yyyy)
> 
> SELECT overlaps('8.6.2005'::date, '9.6.2005'::date, '9.6.2005'::date, 
> '9.6.2005'::date); 
> returns false

I can reproduce it here.  I'm not sure if this is a feature, but I don't
think so.

-- 
Alvaro Herrera (<alvherre[a]surnet.cl>)
"Siempre hay que alimentar a los dioses, aunque la tierra esté seca" (Orual)

On 5/27/05, Mario Weilguni wrote:
> I've quite some trouble with the overlaps function:
> SELECT overlaps('9.6.2005'::date, '9.6.2005'::date, '9.6.2005'::date,
> '9.6.2005'::date);
> returns true (these are german timestamps dd.mm.yyyy)
>
> SELECT overlaps('8.6.2005'::date, '9.6.2005'::date, '9.6.2005'::date,
> '9.6.2005'::date);
> returns false
>
> Is this a bug or a (quite strange) feature?

It is consistent with the SQL standard:
<quote>
<overlaps predicate> uses the operator OVERLAPS to determine whether
or not two chronological periods overlap in time. A chronological
period is specified aither as a pair of datetimes (starting and
ending) or as a starting datetime and an interval. If the length of
the period is greater than 0 (zero), then the period consists of all
points of time greater than or equal to the lower endpoint, and less
than the upper endpoint. If the length of the period is equal to 0
(zero), then the period consists of a single point in time, the lower
endpoint. Two periods overlap if they have at least one point in
common.
</quote> ISO/IEC 9075-2:2003 4.6 Datetimes and intervals.

For those who prefer BNF look at 8.13 <overlaps predicate> in said standard.

Jochem

Mario Weilguni <mweilguni@sime.com> writes:
> I've quite some trouble with the overlaps function:
> SELECT overlaps('9.6.2005'::date, '9.6.2005'::date, '9.6.2005'::date, 
> '9.6.2005'::date);
> returns true (these are german timestamps dd.mm.yyyy)

> SELECT overlaps('8.6.2005'::date, '9.6.2005'::date, '9.6.2005'::date, 
> '9.6.2005'::date); 
> returns false

> Is this a bug or a (quite strange) feature?

AFAICS it is per spec.  SQL99 defines the result of
(S1, T1) OVERLAPS (S2, T2)

(where S1 <= T1 and S2 <= T2, else swap pairs of values to make this so)
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 ) )
 

Your first case has S1 = S2, so it will be TRUE as long as T1 and T2
are both nonnull, according to the third clause.  Your second case
has S1 < S2, so the definition reduces to             NOT ( S2 >= T1 AND T2 >= T1 )

and since in fact those three values are all equal, the NOT is false.

I think they may have intended to treat each time interval
as the half-open interval [S,T), that is S <= time < T.  However
that would leave a zero-length interval as completely empty and
thereby arguably not overlapping anything ... which they didn't
make it do.
        regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

> I think they may have intended to treat each time interval
> as the half-open interval [S,T), that is S <= time < T.  However
> that would leave a zero-length interval as completely empty and
> thereby arguably not overlapping anything ... which they didn't
> make it do.

Well an empty interval would be just as inconsistent. I guess they wanted
half-open intervals and they had a problem with zero-length intervals one way
or the other and just chose the one that seemed most useful.

-- 
greg

On 5/28/05, Tom Lane wrote:
>
> I think they may have intended to treat each time interval
> as the half-open interval [S,T), that is S <= time < T.  However
> that would leave a zero-length interval as completely empty and
> thereby arguably not overlapping anything ... which they didn't
> make it do.

IIRC Jim Melton wrote in one of his books (SQL:1999 or Advanced
SQL:1999, I don't remember) that the intention was to allow easy diary
type comparisons. If you have 2 appointments both with a start and an
end date of 2005-06-09, they overlap. If you have appointments from
08:00 to 10:00 and from 10:00 to 12:00 on that same day, they don't
overlap.

Jochem