Thread: "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:
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.
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;
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
Re: "two time periods with only an endpoint in common do not overlap" ???
From
"David G. Johnston"
Date:
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.
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]
Why don't they overlap, given that they share a common date?
[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.
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: false > > Why /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]:
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: false >> >> Why /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:
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: false
>>
>> Why /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
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
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: false
>>
>> Why /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', '[]')
> 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
>>
--
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> 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: false
>>
>> Why /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.
--
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
Re: "two time periods with only an endpoint in common do not overlap" ???
From
"David G. Johnston"
Date:
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:
Prima facie, if you were told "numbers in the range 0-10", would you really think, "ah, they really mean 0 through 9"?
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.
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.
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? > ---------- > 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 '[]'. 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
Re: "two time periods with only an endpoint in common do not overlap" ???
From
"David G. Johnston"
Date:
On Friday, October 15, 2021, Ron <ronljohnsonjr@gmail.com> wrote:
Prima facie, if you were told "numbers in the range 0-10", would you really think, "ah, they really mean 0 through 9"?
I would indeed default to both endpoints of the range being inclusive. I also begin counting at one, not zero. I’ve long gotten past being surprised when computer science and my defaults don’t agree. Choices are made and documented and that works for me.
As for this, documentation I never really gave the wording a second thought before, though I can definitely understand the complaint and like the somewhat wordier, but less linguistically challenging, phrasing the OP suggested (Boundary point, especially by itself, is not an improvement).
David J.
On 16/10/21 18:41, David G. Johnston wrote: > On Friday, October 15, 2021, Ron <ronljohnsonjr@gmail.com> wrote: > > > Prima facie, if you were told "numbers in the range 0-10", would > you really think, "ah, they *really* mean 0 through 9"? > > > I would indeed default to both endpoints of the range being > inclusive. I also begin counting at one, not zero. I’ve long gotten > past being surprised when computer science and my defaults don’t > agree. Choices are made and documented and that works for me. > > As for this, documentation I never really gave the wording a second > thought before, though I can definitely understand the complaint and > like the somewhat wordier, but less linguistically challenging, > phrasing the OP suggested (Boundary point, especially by itself, is > not an improvement). > > David J. > The reason arrays generally start at zero and not one, is efficiency. When indexes are zero based then the displacement in bytes from the start address of x[n] is simply: startAddress + n * sizeOfElement If the start of an array had the index of one, then you have subtract one each time, so the displacement from the start address of x[n] now becomes startAddress + (n - 1) * sizeOfElement Half open intervals make life a lot simpler so it is the natural default, to prevent intervals from having any numbers in common. If you have 3 intervals spanning the range [0, 30), and you are only dealing with integers then you can split the range as: [0, 9] 0 <= x <= 9 [10, 19] 10 <= x <= 19 [20, 29] 10 <= x <= 29 But what if you are dealing with floats? The above arrangement would not work, as 9.78 would not be in any interval, so you need half open intervals, such as: [0, 10) 0 <= x < 10 [10, 20) 10 <= x < 20 [20, 30) 10 <= x < 30 So you know what number each interval starts at, and every number in the range is covered. -Gavin
On 10/15/21 21:54, Ron wrote: >> >> >> 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 '[]'. > > OP refers to the OVERLAP operator (is it an operator), not the tsrange() > function. > Your statement was: "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." I was just pointing out that is not necessarily true. As was pointed out upstream there are good reasons for not having 1:00-2:00 and 2:00-3:00 overlap. As David pointed out it is about following the documented behavior. I still have to remember, on occasion, that BETWEEN actually includes the end points not just points in between them. -- Adrian Klaver adrian.klaver@aklaver.com
Re: "two time periods with only an endpoint in common do not overlap" ???
From
Francisco Olarte
Date:
Ron:
On Fri, 15 Oct 2021 at 20:16, Ron <ronljohnsonjr@gmail.com> wrote:
> > 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.
Right half open intervals are normally choosed because they can fully
cover the real line without overlap. Full open and full closed can
not.
When you have timestamps, or float/doubles, you are trying to
represent a point on a (time) line, although their finite computer
representation is countable.
When you use dates / integers you can use any kind of intervales, as
they are countable, but it is easier if you use the same
representation.
When I do billing I select monthly CDRs in one system on a condicion
on a timestamp column, {setup >= '2021-05-01' and setup<'2021-06-01'}.
Another system as split date/time, and I select on that table using
the same limits, { fecha >= '2021-05-01' and fecha<'2021-06-01' }. I
could use a full closed interval, but half open is easier.
Also, if you cover the countable set of dates with half-open intervals
it has the nice property of having start-range(i)=end_range(i-1),
which is much easier to program.
And half open are easier to generate. If you want to generate 12
element ranges starting at 1 you can do something like
for i=1 to 100 step 12
print i, i+12
which nicely lets you see they are dozens, if you use closed you need
to "print i, i+11" or "print i, i+12-1".
In general it is a thing because it is easier.
FOS