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. +