Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 7BE249FA2CA for ; Mon, 9 Oct 2006 16:57:34 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 84167-10 for ; Mon, 9 Oct 2006 16:57:31 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.182]) by postgresql.org (Postfix) with ESMTP id 0266F9FA111 for ; Mon, 9 Oct 2006 16:57:30 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id d80so2386128pyd for ; Mon, 09 Oct 2006 12:57:29 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=cFxE74lrqwEac/0OdI50PTHSrqlkSrFkv41DTZxYOb8hhLwMkDZ0hEYarg7T4SjG9isC+eFr6Qn9gTjvH7ckyOOFXbt0Kav1k6SZkq9FS5ReucbRaD5sow3B4RPgeok3KwBjPl2DJgwgHARwF3Yo3l0Se2894Lo/0xNOoJr4Y9o= Received: by 10.35.19.9 with SMTP id w9mr13481336pyi; Mon, 09 Oct 2006 12:57:29 -0700 (PDT) Received: by 10.35.78.6 with HTTP; Mon, 9 Oct 2006 12:57:28 -0700 (PDT) Message-ID: Date: Mon, 9 Oct 2006 14:57:28 -0500 From: "Aaron Bono" To: "Jim Nasby" Subject: Re: [HACKERS] timestamp subtraction (was Re: formatting intervals with to_char) Cc: "Tom Lane" , gdavis@refractions.net, pgsql-sql@postgresql.org, pgsql-hackers@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_40097_14013384.1160423848057" References: <4522EC42.1080500@refractions.net> <21619.1160067054@sss.pgh.pa.us> X-Google-Sender-Auth: 84c926f02229a59b X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.415 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200610/94 X-Sequence-Number: 26483 ------=_Part_40097_14013384.1160423848057 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 10/5/06, 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... I am personally of the opinion that display logic should never be put into the database. Applications that rely on the database formatting - that is tightly coupling your application to the database which does not follow good programming principles. None-the-less, the feature would be nice and may be very valuable for reporting. ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ================================================================== ------=_Part_40097_14013384.1160423848057 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 10/5/06, Jim Nasby <jimn@enterprisedb.com> 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...

 
I am personally of the opinion that display logic should never be put into the database.  Applications that rely on the database formatting - that is tightly coupling your application to the database which does not follow good programming principles.

None-the-less, the feature would be nice and may be very valuable for reporting.

==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
================================================================== ------=_Part_40097_14013384.1160423848057--