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 …
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.