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 EBB8B9DC949 for ; Wed, 1 Mar 2006 14:00:04 -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 51073-02 for ; Wed, 1 Mar 2006 14:00:03 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from candle.pha.pa.us (candle.pha.pa.us [70.90.9.53]) by postgresql.org (Postfix) with ESMTP id D7F279DC820 for ; Wed, 1 Mar 2006 14:00:01 -0400 (AST) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.11.6) id k21HxT320151; Wed, 1 Mar 2006 12:59:29 -0500 (EST) From: Bruce Momjian Message-Id: <200603011759.k21HxT320151@candle.pha.pa.us> Subject: Re: [SQL] Interval subtracting In-Reply-To: <4405D445.4000200@markdilger.com> To: Mark Dilger Date: Wed, 1 Mar 2006 12:59:29 -0500 (EST) CC: Tom Lane , pgsql-hackers@postgresql.org X-Mailer: ELM [version 2.4ME+ PL121 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.407 required=5 tests=[AWL=-0.093, RAZOR2_CHECK=0.5] X-Spam-Score: 0.407 X-Spam-Level: X-Archive-Number: 200603/32 X-Sequence-Number: 80430 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. Good question. Should we restrict days to 0 - 30 or -30 - 30? The current system does the later: test=> select justify_days('-45 days'); justify_days ------------------ -1 mons -15 days (1 row) test=> select justify_days('1 month -45 days'); justify_days -------------- -15 days (1 row) test=> select justify_days('1 month -15 days'); justify_days ---------------- 1 mon -15 days (1 row) Should we be adjusting the last one? I am unsure. Comments? -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. +