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

From: Bryn Llewellyn <bryn(at)yugabyte(dot)com>
To: pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: "two time periods with only an endpoint in common do not overlap" ???
Date: 2021-10-14 23:38:38
Message-ID: 820B633E-90C6-40F0-81A0-CAA074E5762F@yugabyte.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

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;

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2021-10-14 23:54:54 Re: "two time periods with only an endpoint in common do not overlap" ???
Previous Message Imre Samu 2021-10-14 21:15:52 Re: Conditional Tables in Postgres