Message-ID: <4523F764.80705@refractions.net>
Date: Wed, 04 Oct 2006 11:03:16 -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: Re: formatting intervals with to_char
References: <4522EC42.1080500@refractions.net>
In-Reply-To: <4522EC42.1080500@refractions.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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@refractions.net