Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id D44DE9FB70F; Tue, 20 Feb 2007 21:07:18 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 78881-02; Tue, 20 Feb 2007 21:07:10 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from momjian.us (momjian.us [70.90.9.53]) by postgresql.org (Postfix) with ESMTP id C17F59FB66F; Tue, 20 Feb 2007 21:07:14 -0400 (AST) Received: (from bruce@localhost) by momjian.us (8.11.6/8.11.6) id l1L17Bm21179; Tue, 20 Feb 2007 20:07:11 -0500 (EST) From: Bruce Momjian Message-Id: <200702210107.l1L17Bm21179@momjian.us> Subject: Re: [HACKERS] timestamp subtraction (was Re: formatting intervals with to_char) In-Reply-To: To: Jim Nasby Date: Tue, 20 Feb 2007 20:07:11 -0500 (EST) CC: Tom Lane , gdavis@refractions.net, pgsql-sql@postgresql.org, pgsql-hackers@postgresql.org X-Mailer: ELM [version 2.4ME+ PL123] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset="US-ASCII" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.072 tagged_above=0 required=5 tests=AWL, BAYES_50, SPF_HELO_PASS, SPF_PASS X-Spam-Level: X-Archive-Number: 200702/211 X-Sequence-Number: 27545 One problem with removing justify_hours() is that this is going to return '24:00:00', rather than '1 day: test=> select '2004-01-02 00:00:00'::timestamptz - '2004-01-01 00:00:00'::timestamptz; ?column? ---------- 24:00:00 (1 row) --------------------------------------------------------------------------- Jim Nasby wrote: > On Oct 5, 2006, at 11:50 AM, Tom Lane wrote: > > regression=# select ('2006-09-15 23:59:00'::timestamp - '2006-09-01 > > 09:30:41'::timestamp); > > ?column? > > ------------------ > > 14 days 14:28:19 > > (1 row) > > > > should be reporting '350:28:19' instead. > > > > This is a hack that was done to minimize the changes in the regression > > test expected outputs when we changed type interval from months/ > > seconds > > to months/days/seconds. But I wonder whether it wasn't a dumb idea. > > It is certainly inconsistent, as noted in the code comments. > > > > I'm tempted to propose that we remove the justify_hours call, and tell > > anyone who really wants the old results to apply justify_hours() to > > the > > subtraction result for themselves. Not sure what the fallout would > > be, > > though. > > I suspect there's applications out there that are relying on that > being nicely formated for display purposes. > > I agree it should be removed, but we might need a form of backwards > compatibility for a version or two... > -- > Jim Nasby jimn@enterprisedb.com > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq -- Bruce Momjian http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +