On Tue, 23 Nov 2021 at 09:44, Chapman Flack <[email protected]> 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.
