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 A1FFC9DC841; Sat, 18 Feb 2006 15:08:32 -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 80370-01; Sat, 18 Feb 2006 15:08:29 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- X-Greylist: from auto-whitelisted by SQLgrey- Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by postgresql.org (Postfix) with ESMTP id C75D29DC84A; Sat, 18 Feb 2006 15:08:27 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 2882E3587E; Sat, 18 Feb 2006 11:03:40 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 234213586F; Sat, 18 Feb 2006 11:03:40 -0800 (PST) Date: Sat, 18 Feb 2006 11:03:39 -0800 (PST) From: Stephan Szabo To: Tom Lane Cc: "Milen A. Radev" , pgsql-sql@postgresql.org, pgsql-hackers@postgresql.org Subject: Re: [SQL] Interval subtracting In-Reply-To: <20402.1140287414@sss.pgh.pa.us> Message-ID: <20060218110129.W9818@megazone.bigpanda.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> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=X-UNKNOWN Content-Transfer-Encoding: QUOTED-PRINTABLE X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.136 required=5 tests=[AWL=0.136] X-Spam-Score: 0.136 X-Spam-Level: X-Archive-Number: 200602/713 X-Sequence-Number: 79851 On Sat, 18 Feb 2006, Tom Lane wrote: > "Milen A. Radev" writes: > > Milorad Poluga =D0=BD=D0=B0=D0=BF=D0=B8=D1=81=D0=B0: > >>> 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#AEN47= 75) > > . 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 >=3D 30 days= =2E > 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 <=3D days < 30, not merely days < 30. What about cases like interval '1 month -99 days', should that turn into interval '-3 mons +21 days' or '-2 mons -9 days'?