[SQL] 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.
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] formatting intervals with to_char
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.
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] timestamp subtraction (was Re: [SQL] formatting intervals
Great, it's nice to see that this might get rolled into one of the next releases. Thanks, Graham. Tom Lane wrote: Michael Glaesemann <[EMAIL PROTECTED]> 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. [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
