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 4C5C89DC9E8 for ; Wed, 1 Mar 2006 17:22:13 -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 96849-02 for ; Wed, 1 Mar 2006 17:22:12 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 4517A9DC9A4 for ; Wed, 1 Mar 2006 17:22:09 -0400 (AST) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 115C130C39; Wed, 1 Mar 2006 22:22:11 +0100 (MET) From: Mark Dilger X-Newsgroups: pgsql.hackers Subject: Re: [SQL] Interval subtracting Date: Wed, 01 Mar 2006 13:21:36 -0800 Organization: Hub.Org Networking Services Lines: 45 Message-ID: <44061060.8080407@markdilger.com> References: <200603012013.k21KDFM21139@candle.pha.pa.us> <14834.1141244891@sss.pgh.pa.us> <44060682.9020505@markdilger.com> <15150.1141246521@sss.pgh.pa.us> <44060C42.50905@markdilger.com> <15320.1141247469@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org To: Tom Lane User-Agent: Mozilla Thunderbird 1.0 (X11/20050130) X-Accept-Language: en-us, en In-Reply-To: <15320.1141247469@sss.pgh.pa.us> X-Enigmail-Version: 0.89.5.0 X-Enigmail-Supports: pgp-inline, pgp-mime To: pgsql-hackers@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.09 required=5 tests=[AWL=0.090] X-Spam-Score: 0.09 X-Spam-Level: X-Archive-Number: 200603/64 X-Sequence-Number: 80462 Tom Lane wrote: > Mark Dilger writes: > >>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 > > > So? If we liked the current behavior we wouldn't be discussing a patch... > > My thought is that justify_hours should reduce that input to > '1 month 93 days 12:00:00' and then justify_days would produce > '4 months 3 days 12:00:00'. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > 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 select justify_days(justify_hours('-2 days -12:00:00'::interval)) justify_days ------------------------- -2 days -12:00:00 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. mark