Re: [SQL] Interval subtracting

Mark Dilger wrote:
> select justify_hours('-73 hours'::interval);
> justify_hours
> -------------------
> -4 days +23:00:00
>
> select justify_days('-62 days'::interval);
> justify_days
> ------------------
> -3 mons +28 days
>
>
> I find the last two results somewhat peculiar, as the new functionality
> pushes the negative values upwards (from hours to days, days to months).
> Changing '-73 hours' to '-3 days -1 hour' might be more intuitive?
> The '-4 days +23 hours' is however consistent with the behavior in the
> other cases.

I don't think we can accept a change that takes a negative and turns it
into a positive and negative. I think the answer to the last one should
be '-2 mons -2 days', which is what it does now:

test=> select justify_days('-62 days'::interval);
justify_days
-----------------
-2 mons -2 days
(1 row)

The open question is whether we should convert a positive and negative
to a positive, or a negative, based on the sign of the highest value,
e.g. convert '1 mons -10 days' to '20 days', and '-1 mons 10 days' to
'-20 days'?

--
Bruce Momjian http://candle.pha.pa.us
SRA OSS, Inc. http://www.sraoss.com

+ If your life is a hard drive, Christ can be your backup. +