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