Re: overlaps() does not work as expected?

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