From: Bruce Momjian <bruce@momjian.us>
To: Jim C. Nasby <jim@nasby.net>
Cc: Jim Nasby <jimn@enterprisedb.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: gdavis@refractions.net
Cc: pgsql-sql@postgresql.org
Cc: pgsql-hackers@postgresql.org
Subject: Re: timestamp subtraction (was Re: [SQL] formatting intervals with to_char)
Date: Mon, 26 Mar 2007 21:50:30 -0400 (EDT)
Message-ID: <200703270150.l2R1oUT28015@momjian.us> (raw)
In-Reply-To: <20070223162426.GI19527@nasby.net>
Do we want to do anything about this for 8.3?
---------------------------------------------------------------------------
Jim C. Nasby wrote:
> Yes, but if it was '2004-01-02 01:00:00'-'2004-01-01 00:00:00' it should
> return 25:00:00, not 1 day 1:00.
>
> I agree with Tom that this should be changed; I'm just arguing that we
> might well need a backwards-compatibility solution for a while. At the
> very least we'd need to make this change very clear to users.
>
> On Tue, Feb 20, 2007 at 08:07:11PM -0500, Bruce Momjian wrote:
> >
> > 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 <bruce@momjian.us> http://momjian.us
> > EnterpriseDB http://www.enterprisedb.com
> >
> > + If your life is a hard drive, Christ can be your backup. +
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: Don't 'kill -9' the postmaster
>
> --
> Jim Nasby jim@nasby.net
> EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruce@momjian.us, jim@nasby.net, jimn@enterprisedb.com, tgl@sss.pgh.pa.us, gdavis@refractions.net, pgsql-hackers@postgresql.org
Subject: Re: timestamp subtraction (was Re: [SQL] formatting intervals with to_char)
In-Reply-To: <200703270150.l2R1oUT28015@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox