Thread: overlaps behaviour - ('2006-03-01'::TimeStamp, '2007-12-01'::TimeStamp) overlaps ('2007-12-01'::TimeStamp, 'Infinity'::TimeStamp)
overlaps behaviour - ('2006-03-01'::TimeStamp, '2007-12-01'::TimeStamp) overlaps ('2007-12-01'::TimeStamp, 'Infinity'::TimeStamp)
From
Marek Lewczuk
Date:
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
Re: overlaps behaviour - ('2006-03-01'::TimeStamp, '2007-12-01'::TimeStamp) overlaps ('2007-12-01'::TimeStamp, 'Infinity'::TimeStamp)
From
Adrian Klaver
Date:
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@comcast.net