> 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?
