Mark Dilger wrote:
> Mark Dilger wrote:
>
>> Tom Lane wrote:
>>
>>> "Milen A. Radev" <milen(at)radev(dot)net> writes:
>>>
>>>> Milorad Poluga напиÑа:
>>>>
>>>>>> SELECT '10 years 1 mons 1 days'::interval - '9 years 10 mons 15
>>>>>> days'::interval
>>>>>> ?column? --------------- 3 mons -14 days
>>>>>> Why not '2 mons 16 days' ?
>>>
>>>
>>>
>>>
>>>> Please read the last paragraph in section 8.5.1.4 of the manual
>>>> (http://www.postgresql.org/docs/8.1/static/datatype-datetime.html#AEN4775)
>>>>
>>>> . It mentions the functions named "justify_days" and "justify_hours"
>>>> that could do what you need.
>>>
>>>
>>>
>>>
>>> justify_days doesn't currently do anything with this result --- it
>>> thinks its charter is only to reduce day components that are >= 30 days.
>>> However, I think a good case could be made that it should normalize
>>> negative days too; that is, the invariant on its result should be
>>> 0 <= days < 30, not merely days < 30. Similarly for justify_hours.
>>> Comments anyone? Patch anyone?
>>
>>
>>
>> Sure, if nobody objects to this change I can write the patch.
>>
>> mark
>
>
> I've modified the code and it now behaves as follows:
>
> select justify_days('3 months -12 days'::interval);
> justify_days
> ----------------
> 2 mons 18 days
>
> select justify_days('3 months -33 days'::interval);
> justify_days
> ---------------
> 1 mon 27 days
>
> select justify_hours('3 months -33 days -12 hours'::interval);
> justify_hours
> ---------------------------
> 3 mons -34 days +12:00:00
>
> select justify_days(justify_hours('3 months -33 days -12
> hours'::interval));
> justify_days
> ------------------------
> 1 mon 26 days 12:00:00
>
> 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.
>
> Thoughts? I will package this up into a patch fairly soon.
>
> mark
The patch is attached. Since the functionality is being intentionally changed,
not surprisingly the regression tests for timestamp, timestamptz and horology
failed. The regression.diffs are also attached.
I intended to update the docs for justify_days and justify_hours, but the docs
don't detail the behavior at a sufficient level for any change to be warranted.
mark