Re: Where is the tsrange() function documented? - Mailing list pgsql-general

From Bryn Llewellyn
Subject Re: Where is the tsrange() function documented?
Date
Msg-id 9B9DB6FE-6A6B-4D37-A767-85FD8E06D237@yugabyte.com
Whole thread Raw
In response to Re: Where is the tsrange() function documented?  (Adrian Klaver <adrian.klaver@aklaver.com>)
Responses Where is the tsrange() function documented?
Re: Where is the tsrange() function documented?
List 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?

pgsql-general by date:

Previous
From: Bryn Llewellyn
Date:
Subject: Re: Where is the tsrange() function documented?
Next
From: "David G. Johnston"
Date:
Subject: Where is the tsrange() function documented?