Обсуждение: BUG #6124: overlaps

The following bug has been logged online:

Bug reference:      6124
Logged by:          Ludek Bouska
Email address:      ludek@bouska.info
PostgreSQL version: 8.3.7
Operating system:   FreeBSD 7.2-RELEASE-p3
Description:        overlaps
Details:

script
select ('20110720'::date,'20110721'::date) overlaps
('20110721'::date,'20110721'::date)
gives false.

It is necessary to make it as
select ('20110720'::date,'20110721'::date+'1 day'::Interval) overlaps
('20110721'::date,'20110721'::date)
for the 20110721 be realy overlapped.

On Wed, Jul 20, 2011 at 06:09:48AM +0000, Ludek Bouska wrote:
>
> The following bug has been logged online:
>
> Bug reference:      6124
> Logged by:          Ludek Bouska
> Email address:      ludek@bouska.info
> PostgreSQL version: 8.3.7
> Operating system:   FreeBSD 7.2-RELEASE-p3
> Description:        overlaps
> Details:
>
> script
> select ('20110720'::date,'20110721'::date) overlaps
> ('20110721'::date,'20110721'::date)
> gives false.
>
> It is necessary to make it as
> select ('20110720'::date,'20110721'::date+'1 day'::Interval) overlaps
> ('20110721'::date,'20110721'::date)
> for the 20110721 be realy overlapped.

docs clearly state the reason:
http://www.postgresql.org/docs/current/interactive/functions-datetime.html

This expression yields true when two time periods (defined by their
endpoints) overlap, false when they do not overlap. The endpoints can be
specified as pairs of dates, times, or time stamps; or as a date, time, or
time stamp followed by an interval. When a pair of values is provided,
either the start or the end can be written first; OVERLAPS automatically
takes the earlier value of the pair as the start. Each time period is
considered to represent the half-open interval start <= time < end, unless
start and end are equal in which case it represents that single time
instant. This means for instance that two time periods with only an endpoint
in common do not overlap.

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

hubert depesz lubaczewski <depesz@depesz.com> writes:
> On Wed, Jul 20, 2011 at 06:09:48AM +0000, Ludek Bouska wrote:
>> script
>> select ('20110720'::date,'20110721'::date) overlaps
>> ('20110721'::date,'20110721'::date)
>> gives false.

> docs clearly state the reason:
> http://www.postgresql.org/docs/current/interactive/functions-datetime.html

> This expression yields true when two time periods (defined by their
> endpoints) overlap, false when they do not overlap. The endpoints can be
> specified as pairs of dates, times, or time stamps; or as a date, time, or
> time stamp followed by an interval. When a pair of values is provided,
> either the start or the end can be written first; OVERLAPS automatically
> takes the earlier value of the pair as the start. Each time period is
> considered to represent the half-open interval start <= time < end, unless
> start and end are equal in which case it represents that single time
> instant. This means for instance that two time periods with only an endpoint
> in common do not overlap.

Yeah.  This is an explanation in words of the behavior defined by the
SQL spec.  The actual spec text defines the result of (D1, E1) OVERLAPS
(D2, E2) for the non-interval cases thus:

         3) If D1 is the null value or if E1 < D1, then let S1 = E1 and let
            T1 = D1. Otherwise, let S1 = D1 and let T1 = E1.

         5) If D2 is the null value or if E2 < D2, then let S2 = E2 and let
            T2 = D2. Otherwise, let S2 = D2 and let T2 = E2.

         6) The result of the <overlaps predicate> is the result of the
            following expression:

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

If you work through that you'll see that indeed these values should
produce a false result: we have S2 > S1, S2 >= T1, T2 >= T1.

            regards, tom lane