X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id A7E069DC9A4 for ; Wed, 1 Mar 2006 16:40:37 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 89084-04 for ; Wed, 1 Mar 2006 16:40:38 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.markdilger.com (or-65-40-225-74.sta.sprint-hsd.net [65.40.225.74]) by postgresql.org (Postfix) with ESMTP id 7D67D9DC98B for ; Wed, 1 Mar 2006 16:40:33 -0400 (AST) Received: from [192.168.2.6] ([192.168.2.6]) (TLS: TLSv1/SSLv3,256bits,AES256-SHA) by mail.markdilger.com with esmtp; Wed, 01 Mar 2006 12:45:25 -0800 id 00020D65.440607E5.00004075 Message-ID: <44060682.9020505@markdilger.com> Date: Wed, 01 Mar 2006 12:39:30 -0800 From: Mark Dilger User-Agent: Mozilla Thunderbird 1.0 (X11/20050130) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane CC: Bruce Momjian , pgsql-hackers@postgresql.org Subject: Re: [SQL] Interval subtracting References: <200603012013.k21KDFM21139@candle.pha.pa.us> <14834.1141244891@sss.pgh.pa.us> In-Reply-To: <14834.1141244891@sss.pgh.pa.us> X-Enigmail-Version: 0.89.5.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.06 required=5 tests=[AWL=0.060] X-Spam-Score: 0.06 X-Spam-Level: X-Archive-Number: 200603/56 X-Sequence-Number: 80454 Tom Lane wrote: > Bruce Momjian writes: > >>I don't think we can accept a change that takes a negative and turns it >>into a positive and negative. > > > Yeah, I find the patch's changes to the regression results pretty > disturbing. > > Perhaps the correct definition ought to be like "if month part >= 0 > then the reduced day part should be between 0 and 30, otherwise the > reduced day part should be between 0 and -30". However there are > still corner cases to worry about. If the original month and day > parts are of different sign, you might not be able to do such a > reduction without changing the sign of the month part, consider > "1 month -95 days". Not clear what to do with this. > > 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". > > regards, tom lane 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 Which would mean that '1 month -95 days -12 hours' should justify to "-2 months -5 days -12 hours" rather than "-2 months -6 days 12 hours", but that '1 month -15 days -12 hours" would justify to '14 days 12 hours' rather than '15 days -12 hours'. Is this correct? mark