Re: [SQL] Interval subtracting

From: Mark Dilger <pgsql(at)markdilger(dot)com>
To: Mark Dilger <pgsql(at)markdilger(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: [SQL] Interval subtracting
Date: 2006-03-01 18:39:58
Message-ID: 4405EA7E.5050704@markdilger.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches pgsql-sql

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

Attachment Content-Type Size
patch text/plain 1.5 KB
regression.diffs text/plain 46.6 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Jim Nasby 2006-03-01 18:55:32 FW: Rép. : Re: [PERFORM] Bad plan on a view ([Congés])
Previous Message Robert Treat 2006-03-01 18:31:44 Re: Looking for a tool to "*" pg tables as ERDs

Browse pgsql-patches by date

  From Date Subject
Next Message Bruce Momjian 2006-03-01 20:13:15 Re: [SQL] Interval subtracting
Previous Message Jim C. Nasby 2006-03-01 18:24:28 Re: [SQL] Interval subtracting

Browse pgsql-sql by date

  From Date Subject
Next Message Andrew Sullivan 2006-03-01 18:47:09 Re: Change date format through an environmental variable?
Previous Message Andrew Sullivan 2006-03-01 18:28:03 Re: Replication - state of the art?