From: Mark Dilger <pgsql@markdilger.com>
Subject: Re: [SQL] Interval subtracting
Date: Wed, 01 Mar 2006 13:52:16 -0800
Organization: Hub.Org Networking Services
Lines: 63
Message-ID: <44061790.1040302@markdilger.com>
References: <44061060.8080407@markdilger.com>
	<200603012145.k21LjRX11928@candle.pha.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
To: Bruce Momjian <pgman@candle.pha.pa.us>
User-Agent: Mozilla Thunderbird 1.0 (X11/20050130)
In-Reply-To: <200603012145.k21LjRX11928@candle.pha.pa.us>
To: pgsql-hackers@postgresql.org

Bruce Momjian wrote:
> Mark Dilger wrote:
> 
>>Your proposal is that justify_hours borrows 24 hours from the days column in 
>>order to bring the -12 hours up to a positive 12 hours.  Should it only do that 
>>if the days column is a positive number?  What if it is negative?
>>
>>I think we all agree on the following but nobody is explicitly saying so:
>>
>>   select justify_days(justify_hours('2 days -12:00:00'::interval))
>>         justify_days
>>   -------------------------
>>    1 day 12:00:00
> 
> 
> Right.
> 
> 
>>   select justify_days(justify_hours('-2 days -12:00:00'::interval))
>>         justify_days
>>   -------------------------
>>    -2 days -12:00:00
> 
> 
> Right, unchanged.
> 
> 
>>Am I correct that the second case should still have negative hours?  If so, then 
>>justify_hours(...) needs to examine the sign of the days and months portion of 
>>the interval while performing its work.
> 
> 
> Yes, it would need to look at both, and this opens a new problem. 
> Imagine this:
> 
> 	'1 mons -2 days -12:00:00'
> 
> Which sign do we head to for this?  For justify_hours, if we don't look
> at the months it remains unchange, but calling justify_days we get:
> 
> 	'28 days -12:00:00'
> 
> which is wrong (negative and positive).  Now if we knew justify_days was
> going to be called we would have had justify_hours return '-3 days
> 12:00:00' so the final result after calling justify_days would be '27
> days 12:00:00'.
> 
> My head hurts.
> 

I am just now testing a patch which handles all of this.  justify_hours *makes 
no change to months or days*, but it examines them both to determine if the 
total amount of time represented there is positive or negative.  It then makes 
sure that the hours have the same sign.

Of course, if you never get around to calling justify_days, you'll have mixed 
signs in your results.  But if days and months have different signs to begin 
with, then that isn't the fault of justify_hours, so we really haven't done any 
harm.

I'll be posting the patch shortly.

mark