Re: [RFC] ASOF Join

On 11/23/21 10:41, Isaac Morland wrote:
> Umm, it's definitely negative:
>
> odyssey=> select '1 month -31 days +12:00:00'::interval < '0
> months'::interval;
> ----------
> t

Well, what you've shown here is that it's "negative" according to
an arbitrary total ordering imposed in interval_cmp_value for the purpose
of making it indexable in a btree ...

> It's just that due to the complexities of our calendar/time systems, adding
> it to a timestamp can move the timestamp in either direction:

... and this is just another way of saying that said arbitrary choice of
btree ordering can't be used to tell you whether the interval is
semantically positive or negative. (Of course, for a great many intervals,
the two answers will be the same, but they're still answers to different
questions.)

> I'm working on a patch to add abs(interval) so I noticed this. There are
> lots of oddities, including lots of intervals which compare equal to 0 but
> which can change a timestamp when added to it, but as presently designed,
> this particular interval compares as negative.

It's no use—it's oddities all the way down. You can shove them off to one
side of the desk or the other depending on your intentions of the moment,
but they're always there. If you want to put intervals in a btree, you
can define a total ordering where all days are 24 hours and all months
are 30 days, and then there are no oddities in your btree, they're just
everywhere else. Or you can compare your unknown interval to a known
one like '0 months' and say you know whether it's "negative", you just
don't know whether it moves a real date forward or back. Or you can see
what it does to a real date, but not know whether it would precede or
follow some other interval in a btree.

Regards,
-Chap