Re: [SQL] Interval subtracting

Bruce Momjian wrote:
> Mark Dilger wrote:
>
>>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
>
>
> Right.
>
>
>> select justify_days(justify_hours('-2 days -12:00:00'::interval))
>> justify_days
>> -------------------------
>> -2 days -12:00:00
>
>
> Right, unchanged.
>
>
>>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.
>
>
> Yes, it would need to look at both, and this opens a new problem.
> Imagine this:
>
> '1 mons -2 days -12:00:00'
>
> Which sign do we head to for this? For justify_hours, if we don't look
> at the months it remains unchange, but calling justify_days we get:
>
> '28 days -12:00:00'
>
> which is wrong (negative and positive). Now if we knew justify_days was
> going to be called we would have had justify_hours return '-3 days
> 12:00:00' so the final result after calling justify_days would be '27
> days 12:00:00'.
>
> My head hurts.
>

I am just now testing a patch which handles all of this. justify_hours *makes
no change to months or days*, but it examines them both to determine if the
total amount of time represented there is positive or negative. It then makes
sure that the hours have the same sign.

Of course, if you never get around to calling justify_days, you'll have mixed
signs in your results. But if days and months have different signs to begin
with, then that isn't the fault of justify_hours, so we really haven't done any
harm.

I'll be posting the patch shortly.

mark