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 2F4D59DC85C for ; Wed, 1 Mar 2006 16:13:48 -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 83276-08 for ; Wed, 1 Mar 2006 16:13:48 -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 98AA09DC821 for ; Wed, 1 Mar 2006 16:13:45 -0400 (AST) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.11.6) id k21KDFM21139; Wed, 1 Mar 2006 15:13:15 -0500 (EST) From: Bruce Momjian Message-Id: <200603012013.k21KDFM21139@candle.pha.pa.us> Subject: Re: [SQL] Interval subtracting In-Reply-To: <4405E2AD.6030404@markdilger.com> To: Mark Dilger Date: Wed, 1 Mar 2006 15:13:15 -0500 (EST) CC: pgsql-hackers@postgresql.org, Tom Lane 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.409 required=5 tests=[AWL=-0.091, RAZOR2_CHECK=0.5] X-Spam-Score: 0.409 X-Spam-Level: X-Archive-Number: 200603/45 X-Sequence-Number: 80443 Mark Dilger wrote: > 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. I don't think we can accept a change that takes a negative and turns it into a positive and negative. I think the answer to the last one should be '-2 mons -2 days', which is what it does now: test=> select justify_days('-62 days'::interval); justify_days ----------------- -2 mons -2 days (1 row) The open question is whether we should convert a positive and negative to a positive, or a negative, based on the sign of the highest value, e.g. convert '1 mons -10 days' to '20 days', and '-1 mons 10 days' to '-20 days'? -- 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. +