From: | Bryn Llewellyn <bryn(at)yugabyte(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Where is the tsrange() function documented? |
Date: | 2021-10-19 05:11:32 |
Message-ID: | 9B9DB6FE-6A6B-4D37-A767-85FD8E06D237@yugabyte.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> Adrian wrote:
>
>> Bryn wrote:
>>
>> I found my way to this by guesswork and good luck. (I happen to be using PG Version 13.5. But I don't suppose that this matters.)… But I can't formulate a search that finds it using the doc site's intrinsic search.
>
> Where exactly did you search? When I tried in either the overall site or doc site search with 'tsrange()' it offered :
>
> https://www.google.com/url?q=https://www.postgresql.org/docs/14/rangetypes.html&source=gmail-imap&ust=1635222336000000&usg=AOvVaw0uLLwEWap7ttpvbTC1bEb8
>
> as the first choice. This encompasses the link David G. Johnston posted.
Yes, I did find that page, too. I see now that I should have search for "construct" or simply noticed "Constructing Ranges" in the page's ToC.
But I got to where I was with "\df tsrange" because there's usually a system-defined function for every typecast. When I saw there there was a three-argument overload I guessed that the text arg might accept '[)' and so on. So I tried to find the doc for the tsrange() function.
I could've searched in the page for '[)' — but I'm afraid that it didn't occur to me.
Or I might have read enough to find this
… constructor function with the same name as the range type… the three-argument form constructs a range with bounds of the form specified by the third argument. The third argument must be one of the strings “()”, “(]”, “[)”, or “[]”.
But I searched only for "tsrange". This gave me no relevant hit. And that's when I lost patience.
By the way, I was surprised when I tried this:
with c as (
select
'2000-01-01'::timestamp as t1,
'2000-01-10'::timestamp as t2,
'2000-01-20'::timestamp as t3)
select
( tsrange(t1, t3, '[)') && tsrange(t2, t2, '[)') )::text as "range result",
( (t1, t3) overlaps (t2, t2) )::text as "overlaps result"
from c;
and got this:
range result | overlaps result
--------------+-----------------
false | true
I can't find anything, neither on the page in question here on Range Types nor in the doc on the overlaps operator, about the semantics for when a duration collapses to an instant. Am I missing this too?
It seems to me that overlaps" does it right (also in all the other scenarios like instant at the start of a duration, instant at the end of a duration, and two coinciding instants. And it seems, therefore, that && does it wrong. (It always gives false when at least one of the durations is an instant.)
What's the rationale for this asymmetry in semantics?
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2021-10-19 05:25:25 | Where is the tsrange() function documented? |
Previous Message | Bryn Llewellyn | 2021-10-19 04:34:30 | Re: Where is the tsrange() function documented? |