Re: [SQL] Interval subtracting

Tom Lane wrote:
> Mark Dilger <pgsql(at)markdilger(dot)com> writes:
>
>>Tom Lane wrote:
>>
>>>I guess I would expect a good result to satisfy one of these three
>>>cases:
>>> * month > 0 and 0 <= day < 30
>>> * month < 0 and -30 < day <= 0
>>> * month = 0 and -30 < day < 30
>>>If you believe that then "1 month -95 days" should justify to
>>>"-2 months -5 days".
>
>
>>How would you expect justify_hours to behave? I extrapolate from your rules
>>above that:
>
>
>> * month > 0 and 0 <= day < 30 and 0 <= hours < 24
>> * month < 0 and -30 < day <= 0 and -24 < hours <= 0
>> * month = 0 and -30 < day <= 0 and -24 < hours <= 0
>> * month = 0 and 0 <= day < 30 and 0 <= hours < 24
>
>
> Hmmm ... I think it would be better if the two functions were
> independent, if possible. Your spec above implies that justify_hours
> implicitly does justify_days as well, which seems a bit restrictive.
>
> Furthermore, justify_hours should only assume that 1 day == 24 hours,
> which while broken by DST is still a lot solider assumption than
> justify_days' 1 month == 30 days. I can well believe that a lot of
> people only want to make the first assumption.
>
> So I'm inclined to think that justify_hours is responsible for reducing
> the seconds part to less-than-24-hours and pushing any overflow into the
> days part (but not touching months), while justify_days is responsible
> for reducing the days part to less-than-30-days and pushing any overflow
> into the months part (but not touching seconds). If you want both you
> apply both functions, probably in that order. (I wonder if there are
> any cases where applying justify_days before justify_hours would be
> useful. Offhand I can't see one ...)
>
> regards, tom lane

I did not mean to imply that the two functions would be calling each other.
Rather, I thought that a user should get sensible results if they called them
both together. 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

which seems inconsistent with the intent of the patch. Shouldn't the patched
version return '4 mons 3 days 12:00:00' instead?

mark