timestamp subtraction (was Re: formatting intervals with to_char)

Hi,

I'm trying to format the output of a time interval so that it displays
as HH:MM:SS no matter how many days it spans. So for instance, an
interval of 2 days 4 hours and 0 minutes would look something like
"52:00:00". 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. For instance, the following query returns this time interval:

Query:
select ('2006-09-15T23:59:00'::timestamp - '2006-09-01
09:30:41'::timestamp);

Result:
14 days 14:28:19

But when I run to_char on this with HH24, it doesn't take into effect
the number of days:

Query:
select to_char(('2006-09-15T23:59:00'::timestamp - '2006-09-01
09:30:41'::timestamp), 'HH24:MI:SS');

Result:
14:28:19

It just gives me the offset of hours, min, seconds on that 14th day.
The result I'm looking for is: 350:28:19

What am I doing wrong, or how can I get this desired output? Thanks,

--
Graham Davis
Refractions Research Inc.
gdavis(at)refractions(dot)net

I haven't heard any replies from this, so in the meantime I've found a
hacky way to get the output I desire. I'm basically calculating the
hours on the fly and piecing together a formatted string with
concatenations like this:

SELECT
(((EXTRACT(day from time_idle)*24)+EXTRACT(hour from time_idle)) || ':' ||
EXTRACT(minute from time_idle) || ':' || EXTRACT(second from
time_idle))::interval AS myinterval
FROM
( SELECT ('2006-09-15T23:59:00'::timestamp - '2006-09-01
09:30:41'::timestamp) AS time_idle) FROM_TABLE;

If anyone knows a better/proper way to get this result, let me know.
Thanks,

Graham.

Graham Davis wrote:

> Hi,
>
> I'm trying to format the output of a time interval so that it displays
> as HH:MM:SS no matter how many days it spans. So for instance, an
> interval of 2 days 4 hours and 0 minutes would look something like
> "52:00:00". 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. For instance, the following query returns this time
> interval:
>
> Query:
> select ('2006-09-15T23:59:00'::timestamp - '2006-09-01
> 09:30:41'::timestamp);
>
> Result:
> 14 days 14:28:19
>
> But when I run to_char on this with HH24, it doesn't take into effect
> the number of days:
>
> Query:
> select to_char(('2006-09-15T23:59:00'::timestamp - '2006-09-01
> 09:30:41'::timestamp), 'HH24:MI:SS');
>
> Result:
> 14:28:19
>
> It just gives me the offset of hours, min, seconds on that 14th day.
> The result I'm looking for is: 350:28:19
>
> What am I doing wrong, or how can I get this desired output? Thanks,
>

--
Graham Davis
Refractions Research Inc.
gdavis(at)refractions(dot)net

On 10/4/06, Graham Davis <gdavis(at)refractions(dot)net> wrote:
>
> I haven't heard any replies from this, so in the meantime I've found a
> hacky way to get the output I desire. I'm basically calculating the
> hours on the fly and piecing together a formatted string with
> concatenations like this:
>
> SELECT
> (((EXTRACT(day from time_idle)*24)+EXTRACT(hour from time_idle)) || ':' ||
> EXTRACT(minute from time_idle) || ':' || EXTRACT(second from
> time_idle))::interval AS myinterval
> FROM
> ( SELECT ('2006-09-15T23:59:00'::timestamp - '2006-09-01
> 09:30:41'::timestamp) AS time_idle) FROM_TABLE;
>
> If anyone knows a better/proper way to get this result, let me know.

That would be my approach though you could also replace:

EXTRACT(minute from time_idle) || ':' || EXTRACT(second from time_idle)

with:

to_char(time_idle, ':MM:SS')

If you really want to clean up your SQL you could create a function that
takes any timestamp and then make the function IMMUTABLE to tell postgresql
it doesn't need to rederive the results every time.

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

Graham Davis <gdavis(at)refractions(dot)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

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(at)seespotcode(dot)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(at)enterprisedb(dot)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(at)seespotcode(dot)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(at)refractions(dot)net

On 10/5/06, Jim Nasby <jimn(at)enterprisedb(dot)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
==================================================================

On Mon, Oct 09, 2006 at 02:57:28PM -0500, Aaron Bono wrote:
> On 10/5/06, Jim Nasby <jimn(at)enterprisedb(dot)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(at)nasby(dot)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

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(at)enterprisedb(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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(at)enterprisedb(dot)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(at)momjian(dot)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(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

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(at)enterprisedb(dot)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(at)momjian(dot)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(at)nasby(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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(at)enterprisedb(dot)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(at)momjian(dot)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(at)nasby(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +