Message-ID: <44060C42.50905@markdilger.com>
Date: Wed, 01 Mar 2006 13:04:02 -0800
From: Mark Dilger <pgsql@markdilger.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20050130)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: Bruce Momjian <pgman@candle.pha.pa.us>,
  pgsql-hackers@postgresql.org
Subject: Re: [SQL] Interval subtracting
References: <200603012013.k21KDFM21139@candle.pha.pa.us>
	<14834.1141244891@sss.pgh.pa.us> <44060682.9020505@markdilger.com>
	<15150.1141246521@sss.pgh.pa.us>
In-Reply-To: <15150.1141246521@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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