Re: [SQL] Interval subtracting

Tom Lane wrote:
> Mark Dilger <pgsql(at)markdilger(dot)com> writes:
>
>>The current code (without the patch) behaves as follows:
>
>
>> select justify_days(justify_hours('1 month 95 days -36:00:00'::interval));
>> justify_days
>> -------------------------
>> 4 mons 4 days -12:00:00
>
>
> So? If we liked the current behavior we wouldn't be discussing a patch...
>
> My thought is that justify_hours should reduce that input to
> '1 month 93 days 12:00:00' and then justify_days would produce
> '4 months 3 days 12:00:00'.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

Your proposal is that justify_hours borrows 24 hours from the days column in
order to bring the -12 hours up to a positive 12 hours. Should it only do that
if the days column is a positive number? What if it is negative?

I think we all agree on the following but nobody is explicitly saying so:

select justify_days(justify_hours('2 days -12:00:00'::interval))
justify_days
-------------------------
1 day 12:00:00

select justify_days(justify_hours('-2 days -12:00:00'::interval))
justify_days
-------------------------
-2 days -12:00:00

Am I correct that the second case should still have negative hours? If so, then
justify_hours(...) needs to examine the sign of the days and months portion of
the interval while performing its work.

mark