From: Graham Davis <gdavis@refractions.net>
To: pgsql-sql@postgresql.org
Subject: formatting intervals with to_char
Date: Tue, 03 Oct 2006 16:03:30 -0700
Message-ID: <4522EC42.1080500@refractions.net> (raw)
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@refractions.net
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: gdavis@refractions.net
Subject: Re: formatting intervals with to_char
In-Reply-To: <4522EC42.1080500@refractions.net>
* 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