"two time periods with only an endpoint in common do not overlap" ???
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.
Bryn Llewellyn <bryn@yugabyte.com> writes:
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
Huh? Those intervals have lots of points in common, not only a
single point. The documentation is referring to a case like your
second example.
regards, tom lane
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
On Thu, Oct 14, 2021, 16:38 Bryn Llewellyn <bryn@yugabyte.com> wrote:
. This means for instance that two time periods with only an endpoint in
common do not overlap.
A range has two endpoints. The one at the later (end) of the range and the
one at the earlier (start). I suppose rewording it to say "boundary point"
in common would avoid the ambiguity in the use of the word "end".
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
A range has two endpoints. The one at the later (end) of the range and the
one at the earlier (start). I suppose rewording it to say "boundary point"
in common would avoid the ambiguity in the use of the word "end".
Hmm, it seems clear to me in the context of the whole paragraph that
"endpoint" means either end of the range. "Boundary point" would be
longer but I doubt any clearer.
regards, tom lane
David Johnston wrote:
Bryn wrote:
This means for instance that two time periods with only an endpoint in common do not overlap.
A range has two endpoints. The one at the later (end) of the range and the one at the earlier (start). I suppose rewording it to say “boundary point” in common would avoid the ambiguity in the use of the word “end”.
Thanks, David. And thanks, too, to Tom and to Adrian for your prompt replies.
I see that I should have expressed myself more clearly. I never thought that either of the examples that I showed was behaving wrongly. David guessed right: I thought that the wording in the doc was confusing and might be improved.
A period (unless it collapses to an instant) is defined by the two moment values that bound it. (I’m using “moment” to mean a point in absolute time in a way that doesn’t care about the data type.) And when these two moments are distinct, one will be earlier than the other.
In plain English, people talk about, say, a relationship starting and (at least as often happens) ending. You ask “when did the relationship start and end?” Nobody talks about a relationship’s two endpoints. (But maybe they do in a different culture with a different language).
In fact, the PG doc reflects this vernacular usage by giving the signature of one of the overloads thus:
(start1, end1) OVERLAPS (start2, end2)
So I read “endpoint” in the doc I quoted to mean “either end1 or end2” (and, by extension, “startpoint”, if it had been used, to mean “either start1 or start2”.
But the doc wants me to take “endpoint” to mean “either start1, end1, start2, or end2”.
Maybe you think that I’m being too fussy. If so, please forgive me.
Certainly, David’s suggestion to use “boundary point” would be easy to implement, and would be an improvement. I think that I prefer this:
When the end of one period coincides with the start of the other period, then “overlaps” returns “false”.
because it uses the terms in the same way that they are used in the signature.
On 10/14/21 7:02 PM, Adrian Klaver wrote:
[snip]
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
Why /don't/ they overlap, given that they share a common date?
--
Angular momentum makes the world go 'round.
On 10/15/21 06:52, Ron wrote:
On 10/14/21 7:02 PM, Adrian Klaver wrote:
[snip]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: falseWhy /don't/ they overlap, given that they share a common date?
Per the docs:
https://www.postgresql.org/docs/current/functions-datetime.html
" 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."
Which I read as
(DATE '2001-10-29', DATE '2001-10-30') ends at '2001-10-29'
and
(DATE '2001-10-30', DATE '2001-10-31') starts at DATE '2001-10-30'
so no overlap.
--
Angular momentum makes the world go 'round.
--
Adrian Klaver
adrian.klaver@aklaver.com
Ron <ronljohnsonjr@gmail.com> writes:
On 10/14/21 7:02 PM, Adrian Klaver wrote:
SELECT (DATE '2001-10-29', DATE '2001-10-30') OVERLAPS
(DATE '2001-10-30', DATE '2001-10-31');
Result: false
Why /don't/ they overlap, given that they share a common date?
They don't. Per the fine manual [1]https://www.postgresql.org/docs/current/functions-datetime.html:
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.
regards, tom lane
[1]: https://www.postgresql.org/docs/current/functions-datetime.html
On 10/15/21 8:59 AM, Adrian Klaver wrote:
On 10/15/21 06:52, Ron wrote:
On 10/14/21 7:02 PM, Adrian Klaver wrote:
[snip]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: falseWhy /don't/ they overlap, given that they share a common date?
Per the docs:
https://www.postgresql.org/docs/current/functions-datetime.html
" 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."Which I read as
(DATE '2001-10-29', DATE '2001-10-30') ends at '2001-10-29'
and
(DATE '2001-10-30', DATE '2001-10-31') starts at DATE '2001-10-30'
so no overlap.
I was afraid you were going to say that. It's completely bizarre, but seems
to be a "thing" in computer science.
--
Angular momentum makes the world go 'round.
I was afraid you were going to say that. It's completely bizarre, but
seems to be a "thing" in computer science.
Or maybe it's a "math thing". But an overlap implies some length. A
point has no length. Maybe think of them as abutting one another? One
ends /there/, the other starts /there/.
Think of it this way. When someone says they have a meeting from 1-2 and
another from 2-3, do those meetings overlap? They do not, because we're
actually saying the first meeting is from 1:00 through 1:59:59.99999. The
Postgres date ranges are the same way. The starting point is inclusive,
but the ending time is exclusive. So [1:00,2:00), and [2:00,3:00), do not
overlap.
On Fri, Oct 15, 2021 at 2:16 PM Ron <ronljohnsonjr@gmail.com> wrote:
Show quoted text
On 10/15/21 8:59 AM, Adrian Klaver wrote:
On 10/15/21 06:52, Ron wrote:
On 10/14/21 7:02 PM, Adrian Klaver wrote:
[snip]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: falseWhy /don't/ they overlap, given that they share a common date?
Per the docs:
https://www.postgresql.org/docs/current/functions-datetime.html
" 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."
Which I read as
(DATE '2001-10-29', DATE '2001-10-30') ends at '2001-10-29'
and
(DATE '2001-10-30', DATE '2001-10-31') starts at DATE '2001-10-30'
so no overlap.
I was afraid you were going to say that. It's completely bizarre, but
seems
to be a "thing" in computer science.--
Angular momentum makes the world go 'round.
I’m a bit confused by this conversation. Open- and closed-ended ranges behave as I would expect.
select tsrange('2016-01-10 17:00', '2016-01-11 17:00', '[)') && tsrange('2016-01-11 17:00', '2016-01-12 17:00', '[]')
false
select tsrange('2016-01-10 17:00', '2016-01-11 17:00', '[]') && tsrange('2016-01-11 17:00', '2016-01-12 17:00', '[]')
true
Show quoted text
On Oct 15, 2021, 11:27 -0700, Brian Dunavant <dunavant@gmail.com>, wrote:
Think of it this way. When someone says they have a meeting from 1-2 and another from 2-3, do those meetings overlap? They do not, because we're actually saying the first meeting is from 1:00 through 1:59:59.99999. The Postgres date ranges are the same way. The starting point is inclusive, but the ending time is exclusive. So [1:00,2:00), and [2:00,3:00), do not overlap.
On Fri, Oct 15, 2021 at 2:16 PM Ron <ronljohnsonjr@gmail.com> wrote:
On 10/15/21 8:59 AM, Adrian Klaver wrote:
On 10/15/21 06:52, Ron wrote:
On 10/14/21 7:02 PM, Adrian Klaver wrote:
[snip]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: falseWhy /don't/ they overlap, given that they share a common date?
Per the docs:
https://www.postgresql.org/docs/current/functions-datetime.html
" 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."Which I read as
(DATE '2001-10-29', DATE '2001-10-30') ends at '2001-10-29'
and
(DATE '2001-10-30', DATE '2001-10-31') starts at DATE '2001-10-30'
so no overlap.
I was afraid you were going to say that. It's completely bizarre, but seems
to be a "thing" in computer science.--
Angular momentum makes the world go 'round.
On 10/15/21 12:26, Guyren Howe wrote:
I’m a bit confused by this conversation. Open- and closed-ended ranges
behave as I would expect.
What is the part that confused you?
select tsrange('2016-01-10 17:00', '2016-01-11 17:00', '[)') &&
tsrange('2016-01-11 17:00', '2016-01-12 17:00', '[]')
falseselect tsrange('2016-01-10 17:00', '2016-01-11 17:00', '[]') &&
tsrange('2016-01-11 17:00', '2016-01-12 17:00', '[]')
true
--
Adrian Klaver
adrian.klaver@aklaver.com
The numeric ranges 0-10 and 10-19 overlap, just as the time ranges
00:01:00-00:00:02:00 overlaps 00:02:00-00:03:00.
*It's the programmer's responsibility* to say what s/he really means, not
for "the system" to make that choice.
On 10/15/21 1:27 PM, Brian Dunavant wrote:
Think of it this way. When someone says they have a meeting from 1-2 and
another from 2-3, do those meetings overlap? They do not, because we're
actually saying the first meeting is from 1:00 through 1:59:59.99999.
The Postgres date ranges are the same way. The starting point is
inclusive, but the ending time is exclusive. So [1:00,2:00), and
[2:00,3:00), do not overlap.On Fri, Oct 15, 2021 at 2:16 PM Ron <ronljohnsonjr@gmail.com
<mailto:ronljohnsonjr@gmail.com>> wrote:On 10/15/21 8:59 AM, Adrian Klaver wrote:
On 10/15/21 06:52, Ron wrote:
On 10/14/21 7:02 PM, Adrian Klaver wrote:
[snip]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: falseWhy /don't/ they overlap, given that they share a common date?
Per the docs:
https://www.postgresql.org/docs/current/functions-datetime.html
<https://www.postgresql.org/docs/current/functions-datetime.html>
" 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."
Which I read as
(DATE '2001-10-29', DATE '2001-10-30') ends at '2001-10-29'
and
(DATE '2001-10-30', DATE '2001-10-31') starts at DATE '2001-10-30'
so no overlap.
I was afraid you were going to say that. It's completely bizarre, but
seems
to be a "thing" in computer science.--
Angular momentum makes the world go 'round.
--
Angular momentum makes the world go 'round.
On 10/15/21 19:42, Ron wrote:
The numeric ranges 0-10 and 10-19 overlap, just as the time ranges
00:01:00-00:00:02:00 overlaps 00:02:00-00:03:00.*It's the programmer's responsibility* to say what s/he really means,
not for "the system" to make that choice.
It is the programmers choice:
--The canonical form.
--No overlap
select int4range(1,3), int4range(3, 5);
int4range | int4range
-----------+-----------
[1,3) | [3,5)
select int4range(1,3) && int4range(3, 5);
?column?
----------
f
--Making it overlap
select int4range(1,3, '[]'), int4range(3, 5, '[]');
int4range | int4range
-----------+-----------
[1,4) | [3,6)
select int4range(1,3, '[]') && int4range(3, 5, '[]');
?column?
----------
t
There is no straight time range, you would have to use tsrange or
tstzrange. The principle still holds though you can make ranges overlap
or not depending on '[)' or '[]'.
--
Adrian Klaver
adrian.klaver@aklaver.com
On Friday, October 15, 2021, Ron <ronljohnsonjr@gmail.com> wrote:
The numeric ranges 0-10 and 10-19 overlap, just as the time ranges
00:01:00-00:00:02:00 overlaps 00:02:00-00:03:00.*It's the programmer's responsibility* to say what s/he really means, not
for "the system" to make that choice.
The system has canonical representation for discrete element range. The
upper bound is exclusive, the lower bound is inclusive. All the docs are
doing is stating the obvious consequence of that rule.
The “numeric range 0-10” is under specified and thus, as written, one must
abide by the rule, implying [). The programmer is free to fully specify
their range to have the upper bound inclusive, but canonicalization would
then just change it to be “0-11” with the [) implied.
David J.
On 10/15/21 11:35 PM, David G. Johnston wrote:
On Friday, October 15, 2021, Ron <ronljohnsonjr@gmail.com
<mailto:ronljohnsonjr@gmail.com>> wrote:The numeric ranges 0-10 and 10-19 overlap, just as the time ranges
00:01:00-00:00:02:00 overlaps 00:02:00-00:03:00.*It's the programmer's responsibility* to say what s/he really means,
not for "the system" to make that choice.The system has canonical representation for discrete element range. The
upper bound is exclusive, the lower bound is inclusive. All the docs are
doing is stating the obvious consequence of that rule.The “numeric range 0-10” is under specified and thus, as written, one must
abide by the rule, implying [). The programmer is free to fully specify
their range to have the upper bound inclusive, but canonicalization would
then just change it to be “0-11” with the [) implied.
Prima facie, if you were told "numbers in the range 0-10", would you really
think, "ah, they *really* mean 0 through 9"?
--
Angular momentum makes the world go 'round.
On 10/15/21 11:08 PM, Adrian Klaver wrote:
On 10/15/21 19:42, Ron wrote:
The numeric ranges 0-10 and 10-19 overlap, just as the time ranges
00:01:00-00:00:02:00 overlaps 00:02:00-00:03:00.*It's the programmer's responsibility* to say what s/he really means, not
for "the system" to make that choice.It is the programmers choice:
--The canonical form.
--No overlap
select int4range(1,3), int4range(3, 5);
int4range | int4range
-----------+-----------
[1,3) | [3,5)select int4range(1,3) && int4range(3, 5);
?column?
----------
f--Making it overlap
select int4range(1,3, '[]'), int4range(3, 5, '[]');
int4range | int4range
-----------+-----------
[1,4) | [3,6)select int4range(1,3, '[]') && int4range(3, 5, '[]');
?column?
----------
tThere is no straight time range, you would have to use tsrange or
tstzrange. The principle still holds though you can make ranges overlap or
not depending on '[)' or '[]'.
OP refers to the OVERLAP operator (is it an operator), not the tsrange()
function.
--
Angular momentum makes the world go 'round.
Ron <ronljohnsonjr@gmail.com> writes:
OP refers to the OVERLAP operator (is it an operator), not the tsrange()
function.
Indeed. SQL92 defines OVERLAP thus:
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 ) )
where S1 is the smaller of the first range's endpoints and T1 is the
larger; similarly S2/T2 are the smaller/larger of the second range's.
(I gloss over the question of what to do with NULL endpoints; but the
apparent redundancies in the above seem to be meant to define what
happens with NULLs.)
I submit that our description using half-open ranges is clearer than
the spec's. Nonetheless, they're equivalent.
regards, tom lane
