Re: overlaps behaviour - ('2006-03-01'::TimeStamp, '2007-12-01'::TimeStamp) overlaps ('2007-12-01'::TimeStamp, 'Infinity'::TimeStamp)

On Monday 23 February 2009 8:10:34 am Marek Lewczuk wrote:
> Hello,
> I can't find SQL definition for OVERLAPS operator so I don't know
> whether following expression's result (false) is appropriate
> behaviour:
> select ('2006-03-01'::TimeStamp, '2007-12-01'::TimeStamp) overlaps
> ('2007-12-01'::TimeStamp, 'Infinity'::TimeStamp)
>
> Can anyone confirm that ? In my understanding of "overlaps" it should
> result true, as those two periods overlaps in 2007-12-01.
>
> psql (PostgreSQL) 8.3.5
>
> Best regards,
> ML

A link to the SQL standard, good luck with understanding it.
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

Some tests

test=# SELECT '2007-12-01'::TimeStamp;
timestamp
---------------------
2007-12-01 00:00:00
(1 row)

test=# select ('2006-03-01'::timestamp, '2007-12-01'::timestamp) overlaps
('2007-12-01'::timestamp, 'Infinity'::timestamp)
;
overlaps
----------
f
(1 row)

test=# select ('2006-03-01'::timestamp, '2007-12-01 00:00:01'::timestamp)
overlaps
('2007-12-01'::timestamp, 'Infinity'::timestamp)
;
overlaps
----------
t

From the docs
In addition to these functions, the SQL OVERLAPS operator is supported:

(start1, end1) OVERLAPS (start2, end2)
(start1, length1) OVERLAPS (start2, length2)

Would seem that in this case OVERLAPS means the end1 must be greater than
start2, not equal to it. In other words actually overlap.
--
Adrian Klaver
aklaver(at)comcast(dot)net