Re: "two time periods with only an endpoint in common do not overlap" ???

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(dot)klaver(at)aklaver(dot)com