Re: [RFC] ASOF Join

On Tue, 23 Nov 2021 at 09:44, Chapman Flack <chap(at)anastigmatix(dot)net> wrote:

> On 11/23/21 02:29, Ilya Anfimov wrote:
> > (*We
> > strangely don't have an absolute value operator on interval, but
> > I think you've got the point*).
>
> Although tangential to the topic, that might be because a PG interval
> is a triple of independently-signed months/days/seconds components.
> An interval like '1 month -31 days +12:00:00' is positive or negative
> depending on the absolute date you apply it to, so what its absolute
> value should be isn't clear in isolation.
>

Umm, it's definitely negative:

odyssey=> select '1 month -31 days +12:00:00'::interval < '0
months'::interval;
?column?
----------
t
(1 row)

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:

odyssey=> select '2021-02-01'::timestamp + '1 month -31 days
+12:00:00'::interval;
?column?
---------------------
2021-01-29 12:00:00
(1 row)

odyssey=> select '2021-03-01'::timestamp + '1 month -31 days
+12:00:00'::interval;
?column?
---------------------
2021-03-01 12:00:00
(1 row)

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.