Thread: Where is the tsrange() function documented?
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.)
Doing "\df tsrange()" gives this:
Schema | Name | Result data type | Argument data types | Type
------------+---------+------------------+----------------------------------------------------------------+------
pg_catalog | tsrange | tsrange | timestamp without time zone, timestamp without time zone | func
pg_catalog | tsrange | tsrange | timestamp without time zone, timestamp without time zone, text | func
------------+---------+------------------+----------------------------------------------------------------+------
pg_catalog | tsrange | tsrange | timestamp without time zone, timestamp without time zone | func
pg_catalog | tsrange | tsrange | timestamp without time zone, timestamp without time zone, text | func
And doing this:
\x on
with c as (
select
'2000-01-15'::timestamp as t1,
'2000-05-15'::timestamp as t2)
select
tsrange(t1, t2, '[]') as r1,
tsrange(t1, t2, '[)') as r2,
tsrange(t1, t2, '(]') as r3,
tsrange(t1, t2, '()') as r4
from c;
\x off
with c as (
select
'2000-01-15'::timestamp as t1,
'2000-05-15'::timestamp as t2)
select
tsrange(t1, t2, '[]') as r1,
tsrange(t1, t2, '[)') as r2,
tsrange(t1, t2, '(]') as r3,
tsrange(t1, t2, '()') as r4
from c;
\x off
gives this:
r1 | ["2000-01-15 00:00:00","2000-05-15 00:00:00"]
r2 | ["2000-01-15 00:00:00","2000-05-15 00:00:00")
r3 | ("2000-01-15 00:00:00","2000-05-15 00:00:00"]
r4 | ("2000-01-15 00:00:00","2000-05-15 00:00:00")
r2 | ["2000-01-15 00:00:00","2000-05-15 00:00:00")
r3 | ("2000-01-15 00:00:00","2000-05-15 00:00:00"]
r4 | ("2000-01-15 00:00:00","2000-05-15 00:00:00")
It's exactly what I was looking for. Now I want to refer colleagues to the PG doc on the tsrange() function.
But I can't formulate a search that finds it using the doc site's intrinsic search.
And I can't even find a single example of it on any site using Google.
Where is it?
On Monday, October 18, 2021, Bryn Llewellyn <bryn@yugabyte.com> wrote:
Where is it?
TOC - Data Types - Range Types:
David J.
On 10/18/21 20:44, Bryn Llewellyn 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.postgresql.org/docs/14/rangetypes.html as the first choice. This encompasses the link David G. Johnston posted. > > And I can't even find a single example of it on any site using Google. > > Where is it? -- Adrian Klaver adrian.klaver@aklaver.com
David Johnston wrote:Bryn wrote:
Where is it?
TOC - Data Types - Range Types:
https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-BUILTIN
Thanks for the instant response, David. That's exactly what I wanted—and in particular "8.17.6. Constructing Ranges and Multiranges":
https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-CONSTRUCT
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;
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
--------------+-----------------
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?
On Monday, October 18, 2021, Bryn Llewellyn <bryn@yugabyte.com> wrote:
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?
Same page:
- includes no points (and will be normalized to 'empty'
SELECT '[4,4)'::int4range;
The asymmetry stems from the definition of the same-valued half-open interval being normalized to an instant by the sql standard when dealing with overlaps but normalizing to empty for our range specification. Neither choice seems wrong on its face. It is what it is at this point - I defer to the commit message and mailing list history for any relevant “why” discussion.
David J.
> On 19 Oct 2021, at 7:11, Bryn Llewellyn <bryn@yugabyte.com> wrote:
>
> 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,
aboutthe semantics for when a duration collapses to an instant. Am I missing this too?
Your mistake is in how you defined an instant as a range:
with c as (
select
'2000-01-01'::timestamp as t1,
'2000-01-10'::timestamp as t2,
'2000-01-20'::timestamp as t3)
select
tsrange(t2, t2, '[)') as empty
, tsrange(t2, t2, '[]') as instant
, ( tsrange(t1, t3, '[)') && tsrange(t2, t2, '[)') )::text as "empty range result"
, ( tsrange(t1, t3, '[)') && tsrange(t2, t2, '[]') )::text as "instant range result"
from c;
empty | instant | empty range result | instant range result
-------+-----------------------------------------------+--------------------+----------------------
empty | ["2000-01-10 00:00:00","2000-01-10 00:00:00"] | false | true
(1 row)
As I read it, an empty range is not considered to overlap anything, regardless of ‘when' it was defined; it gets
normalisedto ‘empty’.
See also the examples in section 8.17.5 for the difference between an empty range and a single point range (what you
callan instant).
Regards,
Alban Hertroys
--
There is always an exception to always.
David Johnston wrote:Bryn wrote:
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?
Same page:
https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-IO (Examples)- includes no points (and will be normalized to 'empty'
SELECT '[4,4)'::int4range;
The asymmetry stems from the definition of the same-valued half-open interval being normalized to an instant by the sql standard when dealing with overlaps but normalizing to empty for our range specification. Neither choice seems wrong on its face. It is what it is at this point - I defer to the commit message and mailing list history for any relevant “why” discussion.
Thanks again, David. And thanks, too, to Alban Hertroys for your reply.
Forgive me for having asked "why the asymmetry". But I've found, over the years, that it helps me enormously to know whether the answer to a "why" question is "unassailable logic leads to it" or "someone made a whimsical choice and it's too late now to change it". Now I have my answer: in this case, "it is what it is" (as David said).
The code that I used when I submitted this question:
select
( tsrange(t1, t3, '[)') && tsrange(t2, t2, '[)') )::text as "range result",
( (t1, t3) overlaps (t2, t2) )::text as "overlaps result"
from …
( tsrange(t1, t3, '[)') && tsrange(t2, t2, '[)') )::text as "range result",
( (t1, t3) overlaps (t2, t2) )::text as "overlaps result"
from …
emphasises the significant overlap in functionality (no pun intended) between the two approaches. More carefully stated, it seems that using ranges gives much greater functionality than "overlaps" while allowing the functionality of the "overlaps" operator to be implemented using the && operator. But with ranges, you also have, for example, the intersection operator to measure the size of the overlap.
But there are caveats such that you'd have to define your own my_overlaps() function with special case code thus:
— Ranges support daterange(), tsrange(), and tstzrange(). But there's nothing for time (or the recommended-against timetz). But "overlaps" supports time, timetz, timestamp, and timestamptz but not date. So my_overlaps(time, time) would need some explicit typecasting code (and a documented convention).
— you'd need to accommodate the fact that "overlaps" doesn't care about the order in which you provide the bounds of each duration, but the definition of a range gives the 22000 error (range lower bound must be less than or equal to range upper bound) if you give the bounds in the wrong order.
— you'd need to express non-empty durations using '[)' and empty durations using '[]' to get the "overlaps" semantics.
Oh well… let me wrestle with this by myself and say "case closed". Your answers were very helpful.