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