Thread: so, does this overlap or not...? - fencepost question on overlaps()

Hi,

This doesn't seem to make sense to me, can someone explain the rationale
behind it?


postgres=# select version();
                                        version
---------------------------------------------------------------------------------------
 PostgreSQL 8.4.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
4.3.3, 64-bit
(1 row)



Range 1 ending on date A does not overlap with range 2 starting on date A:

postgres=# select ('2010-01-01'::date, '2010-01-05'::date) overlaps
('2010-01-05'::date, '2010-01-10'::date);
 overlaps
----------
 f
(1 row)



But it does when range 1 is only a single day:

postgres=# select ('2010-01-05'::date, '2010-01-05'::date) overlaps
('2010-01-05'::date, '2010-01-10'::date);
 overlaps
----------
 t
(1 row)


BTW, it doesn't matter whether one casts to date or timestamp




--
Best,




Frank.

Frank van Vugt <ftm.van.vugt@foxi.nl> writes:
> This doesn't seem to make sense to me, can someone explain the rationale
> behind it?

The rationale is "do what the SQL spec says" ;-)

What the spec says is

              ( 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 ) )

I seem to recall a previous discussion in the PG lists where we
reverse-engineered a plausible explanation of what the standards
committee had in mind when they wrote this, but I don't have time
right now to go looking for it.

            regards, tom lane

Hi Tom,

> The rationale is "do what the SQL spec says" ;-)

can't argue with the standard ;)

> I seem to recall a previous discussion in the PG lists

Good memory !

Adding 'sql standard' to the search options helped, this issue seems to elude
people every now and then, given (amongst others):

http://archives.postgresql.org/pgsql-hackers/2005-05/msg01457.php

http://archives.postgresql.org/pgsql-general/2006-11/msg00763.php

http://archives.postgresql.org/pgsql-general/2006-11/msg00527.php


One might consider adding a single line to the end of 9.9 of the docs that
warns for this behaviour and/or add the specific example....?




Thanks for your help.


--
Best,




Frank.

Frank van Vugt <ftm.van.vugt@foxi.nl> writes:
> One might consider adding a single line to the end of 9.9 of the docs that
> warns for this behaviour and/or add the specific example....?

I put some more explanation and examples into the 9.0 docs:
http://developer.postgresql.org/pgdocs/postgres/functions-datetime.html

            regards, tom lane