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@xxxxxxxxxxx
