Re: overlaps() does not work as expected?

Mario Weilguni <mweilguni(at)sime(dot)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