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 6DD929DC820 for ; Wed, 1 Mar 2006 14:07:17 -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 53861-02 for ; Wed, 1 Mar 2006 14:07:15 -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 226399DC821 for ; Wed, 1 Mar 2006 14:07:14 -0400 (AST) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 24CCF30972; Wed, 1 Mar 2006 19:07:13 +0100 (MET) From: Mark Dilger X-Newsgroups: pgsql.hackers Subject: Re: [SQL] Interval subtracting Date: Wed, 01 Mar 2006 10:06:37 -0800 Organization: Hub.Org Networking Services Lines: 75 Message-ID: <4405E2AD.6030404@markdilger.com> References: <200602181444.06009.milorad.poluga@cores.co.yu> <43F72E27.2060408@radev.net> <200602181536.20605.milorad.poluga@cores.co.yu> <43F7330E.1060907@radev.net> <20402.1140287414@sss.pgh.pa.us> <4405D445.4000200@markdilger.com> Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 8bit X-Complaints-To: usenet@news.hub.org Cc: Tom Lane To: Mark Dilger User-Agent: Mozilla Thunderbird 1.0 (X11/20050130) X-Accept-Language: en-us, en In-Reply-To: <4405D445.4000200@markdilger.com> 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.106 required=5 tests=[AWL=0.106] X-Spam-Score: 0.106 X-Spam-Level: X-Archive-Number: 200603/33 X-Sequence-Number: 80431 Mark Dilger wrote: > Tom Lane wrote: > >> "Milen A. Radev" writes: >> >>> Milorad Poluga написа: >>> >>>>> SELECT '10 years 1 mons 1 days'::interval - '9 years 10 mons 15 >>>>> days'::interval >>>>> ?column? --------------- 3 mons -14 days >>>>> Why not '2 mons 16 days' ? >> >> >> >>> Please read the last paragraph in section 8.5.1.4 of the manual >>> (http://www.postgresql.org/docs/8.1/static/datatype-datetime.html#AEN4775) >>> >>> . It mentions the functions named "justify_days" and "justify_hours" >>> that could do what you need. >> >> >> >> justify_days doesn't currently do anything with this result --- it >> thinks its charter is only to reduce day components that are >= 30 days. >> However, I think a good case could be made that it should normalize >> negative days too; that is, the invariant on its result should be >> 0 <= days < 30, not merely days < 30. Similarly for justify_hours. >> Comments anyone? Patch anyone? > > > Sure, if nobody objects to this change I can write the patch. > > mark I've modified the code and it now behaves as follows: select justify_days('3 months -12 days'::interval); justify_days ---------------- 2 mons 18 days select justify_days('3 months -33 days'::interval); justify_days --------------- 1 mon 27 days select justify_hours('3 months -33 days -12 hours'::interval); justify_hours --------------------------- 3 mons -34 days +12:00:00 select justify_days(justify_hours('3 months -33 days -12 hours'::interval)); justify_days ------------------------ 1 mon 26 days 12:00:00 select justify_hours('-73 hours'::interval); justify_hours ------------------- -4 days +23:00:00 select justify_days('-62 days'::interval); justify_days ------------------ -3 mons +28 days I find the last two results somewhat peculiar, as the new functionality pushes the negative values upwards (from hours to days, days to months). Changing '-73 hours' to '-3 days -1 hour' might be more intuitive? The '-4 days +23 hours' is however consistent with the behavior in the other cases. Thoughts? I will package this up into a patch fairly soon. mark