> On 19 Oct 2021, at 7:11, Bryn Llewellyn <b...@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, 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.
