Re: "two time periods with only an endpoint in common do not overlap" ??? - Mailing list pgsql-general

On 10/14/21 16:38, Bryn Llewellyn wrote:
> I’m quoting this from “9.9. Date/Time Functions and Operators” in the PG 
> Version 14 doc on the “overlaps” operator, here:
> 
> www.postgresql.org/docs/14/functions-datetime.html 
> <http://www.postgresql.org/docs/14/functions-datetime.html>
> 
> It’s the same in “current”—and in the Version 11 doc.
> 
>     «
>     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.
>     »
> 
> 
> I tried this obvious test (using Version 13.4):
> 
> *with c as (
>    select
>      '2000-01-15'::timestamp as start_1,
>      '2000-02-15'::timestamp as start_2,
>      '2000-03-15'::timestamp as common_endpoint)
> select (
>    (start_1, **common_endpoint**) overlaps
>    (start_2, **common_endpoint**)
> )::text
> from c;

This resolves to:

select ('2000-01-15'::timestamp, '2000-03-15'::timestamp) overlaps 
('2000-02-15'::timestamp, '2000-03-15'::timestamp);
  overlaps
----------
  t

which to me looks like an overlap.

What you are referring to is:

select ('2000-01-15'::timestamp, '2000-03-15'::timestamp) overlaps 
('2000-03-15'::timestamp, '2000-03-20'::timestamp);
  overlaps
----------
  f

or the third example in the docs:

SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
        (DATE '2001-10-30', DATE '2002-10-30');
Result: true
SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
        (DATE '2001-10-30', DATE '2002-10-30');
Result: false
SELECT (DATE '2001-10-29', DATE '2001-10-30') OVERLAPS
        (DATE '2001-10-30', DATE '2001-10-31');
Result: false
SELECT (DATE '2001-10-30', DATE '2001-10-30') OVERLAPS
        (DATE '2001-10-30', DATE '2001-10-31');
Result: true


> *
> The result is "true". Seems to me that the doc is therefore wrong—not 
> only as shown by this test but also w.r.t. what reasoning from the 
> account at "half-open interval" says.
> 
> Now consider this:
> 
> *with c as (
>    select
>      '2000-01-15'::timestamp as start,
>      '2000-02-15'::timestamp as common_touchpoint,
>      '2000-03-15'::timestamp as endpoint)
> select (
>    (start, **common_touchpoint**) overlaps
>    (**common_touchpoint**, **endpoint**)
> )::text
> from c;
> *
> The result is now "false".  As it seems to me this is correct w.r.t. 
> what reasoning from the account at "half-open interval" says.
> 
> It also seems to me that whenever the doc derives a predicted result 
> from the stated rules, it's honor bound to substantiate this with a code 
> example.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com