Thread: timestamp subtraction (was Re: [SQL] formatting intervals with to_char)
Graham Davis <gdavis@refractions.net> writes:
> The documentation for to_char states that:
> "|to_char(interval)| formats HH and HH12 as hours in a single day, while
> HH24 can output hours exceeding a single day, e.g. >24."
> However I can not get it to work with time intervals that span more than
> 1 day.
Well, it does in fact print intervals exceeding 24 hours:
regression=# select to_char('48 hours'::interval, 'HH24:MI:SS');to_char
----------48:00:00
(1 row)
However, '48 hours' and '2 days' are not the same thing. The problem
with the case you give is really that timestamp_mi applies justify_hours
to its result --- that is,
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.
regards, tom lane
Re: timestamp subtraction (was Re: [SQL] formatting intervals with to_char)
From
Michael Glaesemann
Date:
On Oct 6, 2006, at 1:50 , Tom Lane wrote: > 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'm tempted to support such a proposal. Is this something that we'd want to do for 8.2? There are some interval range checking fixes I'm working on for 8.3. Perhaps this could be rolled into that as well? Then again, range checking and behavior are two separate things. Considering how late it is in the cycle, perhaps the change in behavior should come in 8.3. Michael Glaesemann grzm seespotcode net
Michael Glaesemann <grzm@seespotcode.net> writes:
> Considering how late it is in the cycle, perhaps the change in
> behavior should come in 8.3.
Yeah, there's not really enough time to think through the consequences
now. I'd like to experiment with it for 8.3 though.
regards, tom lane
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)
Great, it's nice to see that this might get rolled into one of the next releases. Thanks, Graham. Tom Lane wrote: >Michael Glaesemann <grzm@seespotcode.net> writes: > > >>Considering how late it is in the cycle, perhaps the change in >>behavior should come in 8.3. >> >> > >Yeah, there's not really enough time to think through the consequences >now. I'd like to experiment with it for 8.3 though. > > regards, tom lane > > -- Graham Davis Refractions Research Inc. gdavis@refractions.net
On 10/5/06, Jim Nasby <jimn@enterprisedb.com> wrote:
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
==================================================================
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...
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
==================================================================
Re: timestamp subtraction (was Re: [SQL] formatting intervals with to_char)
From
"Jim C. Nasby"
Date:
On Mon, Oct 09, 2006 at 02:57:28PM -0500, Aaron Bono wrote:
> 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.
I agree in general, except most languages have terrible support for
time/date data, so I can see a much bigger case for the database being
able to do it (and it's not like we'll be removing justify_*). Be that
as it may, there are probably apps out there that will break if this is
just changed.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Jim, > I agree in general, except most languages have terrible support for > time/date data, so I can see a much bigger case for the database being > able to do it (and it's not like we'll be removing justify_*). Be that > as it may, there are probably apps out there that will break if this is > just changed. Many. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco
Re: timestamp subtraction (was Re: [SQL] formatting intervals with to_char)
From
Bruce Momjian
Date:
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-0100: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. +
Re: timestamp subtraction (was Re: [SQL] formatting intervals with to_char)
From
"Jim C. Nasby"
Date:
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)
Re: timestamp subtraction (was Re: [SQL] formatting intervals with to_char)
From
Bruce Momjian
Date:
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. +
Re: timestamp subtraction (was Re: [SQL] formatting intervals with to_char)
From
Bruce Momjian
Date:
Added to TODO:
o Have timestamp subtraction not call justify_hours()?
http://archives.postgresql.org/pgsql-sql/2006-10/msg00059.php
---------------------------------------------------------------------------
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. +