Re: [SQL] Interval subtracting

Tom Lane wrote:
> Mark Dilger <pgsql@markdilger.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