Message-ID: <4522EC42.1080500@refractions.net>
Date: Tue, 03 Oct 2006 16:03:30 -0700
From: Graham Davis <gdavis@refractions.net>
Reply-To: gdavis@refractions.net
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.6b) Gecko/20031205 Thunderbird/0.4
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: formatting intervals with to_char
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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