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