From: | Alban Hertroys <haramrae(at)gmail(dot)com> |
---|---|
To: | Bryn Llewellyn <bryn(at)yugabyte(dot)com> |
Cc: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Where is the tsrange() function documented? |
Date: | 2021-10-19 09:29:51 |
Message-ID: | ECD3A1D3-B205-4BA5-948F-A9AF3CB6AB7B@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> On 19 Oct 2021, at 7:11, Bryn Llewellyn <bryn(at)yugabyte(dot)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, about the 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 normalised to ‘empty’.
See also the examples in section 8.17.5 for the difference between an empty range and a single point range (what you call an instant).
Regards,
Alban Hertroys
--
There is always an exception to always.
From | Date | Subject | |
---|---|---|---|
Next Message | Mladen Gogala | 2021-10-19 13:24:40 | Re: Force re-compression with lz4 |
Previous Message | Pawan Sharma | 2021-10-19 09:28:33 | spannerdb migration to PostgreSQL |