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 07C6C9DC841; Sat, 18 Feb 2006 14:30: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 49587-04; Sat, 18 Feb 2006 14:30:16 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 5EC859DC84A; Sat, 18 Feb 2006 14:30:14 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k1IIUEli020403; Sat, 18 Feb 2006 13:30:14 -0500 (EST) To: "Milen A. Radev" cc: pgsql-sql@postgresql.org, pgsql-hackers@postgresql.org Subject: Re: [SQL] Interval subtracting In-reply-to: <43F7330E.1060907@radev.net> References: <200602181444.06009.milorad.poluga@cores.co.yu> <43F72E27.2060408@radev.net> <200602181536.20605.milorad.poluga@cores.co.yu> <43F7330E.1060907@radev.net> Comments: In-reply-to "Milen A. Radev" message dated "Sat, 18 Feb 2006 16:45:34 +0200" Date: Sat, 18 Feb 2006 13:30:14 -0500 Message-ID: <20402.1140287414@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.11 required=5 tests=[AWL=0.110] X-Spam-Score: 0.11 X-Spam-Level: X-Archive-Number: 200602/706 X-Sequence-Number: 79844 "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? regards, tom lane