Re: Add missing function abs (interval)

I've attached a patch for this. Turns out there was a comment in the source
explaining that there is no interval_abs because it's not clear what to
return; but I think it's clear that if i is an interval the larger of i and
-i should be considered to be the absolute value, the same as would be done
for any type; essentially, if the type is orderable and has a meaningful
definition of unary minus, the definition of abs follows from those.

This does have some odd effects, as was observed in the previous discussion
pointed at by John Naylor above (for which thanks!). But those odd effects
are not due to abs(interval) itself but rather due to the odd behaviour of
interval, where values which compare equal to '0'::interval can change a
timestamp when added to it. This in turn comes from what the interval data
type is trying to do combined with the inherently complicated nature of our
timekeeping system.

I have included in the test case some testing of what happens with '1 month
-30 days'::interval, which is "equal" to '0'::interval.

At least one thing concerns me about my code: Given an interval i, I
palloc() space to calculate -i; then either return that or the original
input depending on the result of a comparison. Will I leak space as a
result? Should I free the value if I don't return it?

In addition to adding abs(interval) and related @ operator, I would like to
update interval_smaller and interval_larger to change < and > to <= and >=
respectively. This is to make the min(interval) and max(interval)
aggregates return the first of multiple distinct "equal" intervals,
contrary to the current behaviour:

odyssey=> select max (i) from (values ('1 month -30 days'::interval), ('-1
month 30 days'))t(i);
max
------------------
-1 mons +30 days
(1 row)

odyssey=> select min (i) from (values ('1 month -30 days'::interval), ('-1
month 30 days'))t(i);
min
------------------
-1 mons +30 days
(1 row)

odyssey=>

GREATEST and LEAST already take the first value:

odyssey=> select greatest ('1 month -30 days'::interval, '-1 month 30
days');
greatest
----------------
1 mon -30 days
(1 row)

odyssey=> select least ('1 month -30 days'::interval, '-1 month 30 days');
least
----------------
1 mon -30 days
(1 row)

odyssey=>

On Mon, 29 Mar 2021 at 21:06, Michael Paquier <michael(at)paquier(dot)xyz> wrote:

> On Mon, Mar 29, 2021 at 07:15:19PM -0400, John Naylor wrote:
> > Looking in the archives, I see this attempt that you can build upon:
> >
> https://www.postgresql.org/message-id/flat/CAHE3wggpj%2Bk-zXLUdcBDRe3oahkb21pSMPDm-HzPjZxJn4vMMw%40mail.gmail.com
>
> I see no problem with doing something more here. If you can get a
> patch, please feel free to add it to the next commit fest, for
> Postgres 15:
> https://commitfest.postgresql.org/33/
> --
> Michael
>