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

Tom Lane wrote:
 > Mark Dilger <pgsql@markdilger.com> writes:
 >
 >>Am I correct that the second case should still have negative hours?
 >
 >
 > Yes...
 >
 >
 >>If so, then justify_hours(...) needs to examine the sign of the days
 >>and months portion of the interval while performing its work.
 >
 >
 > No, it should ignore the months part completely, IMHO.  You are just
 > confusing matters by using both functions in your examples, as then
 > it's not clear which does what.
 >
 > 			regards, tom lane

I like the idea that a person has some justify-path by which they can get all 
the signs to match.  With the patch that I just posted, this is accomplished as 
follows:

   justify_days(justify_hours(...))

Regardless of the particular weirdness of the signs in the original interval. 
But the patch also leaves open the possibility that you don't want the hours 
touched, perhaps because you're dealing with a daylight savings time period and 
can't accept the concept of a 24-hour day.  In that case:

   justify_days(...)

will get the sign on the months and days to match each other, though perhaps not 
match the hours.  In the event that you want to justify the hours, but can't 
accept having the days justified (because you have a non-30 day month), then you 
can call:

   justify_hours(...)

and get the sign on the hours portion to match the overall intent of the 
interval (positive or negative) without being forced to actually change the way 
the days and months are being represented.

This overall design seems more flexible than Tom's recent post in which he 
stated that justify_days should call justify_hours internally.  I tend not to 
agree.  However, it wouldn't hurt to have a justify_interval(...) function which 
does justify both in one shot.

mark