Message-ID: <41D9CF06.1010800@onesystem.com>
Date: Mon, 03 Jan 2005 16:02:30 -0700
From: Edmund Bacon <ebacon@onesystem.com>
User-Agent: Mozilla Thunderbird 0.9 (X11/20041103)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Cc: Jamison Roberts <jamisonroberts@gmail.com>
Subject: Re: Formatting an Interval
References: <238e52230412310837183fb730@mail.gmail.com>
	<20041231194118.GA23987@winnie.fuhr.org>
In-Reply-To: <20041231194118.GA23987@winnie.fuhr.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


Michael Fuhr wrote:
> On Fri, Dec 31, 2004 at 11:37:32AM -0500, Jamison Roberts wrote:
> 
> 
>>All of the functions that i've looked at seem to only extract parts
>>from Intervals.  What I need to do is to format the interval.  For
>>instance, I have a Interval with the value 1 day 07:57:52.  I would
>>like that in HH:MM:SS.  So in the example the output would be
>>31:57:52.
> 
> 
> I'm not aware of a built-in way to get such a format; somebody
> please correct me if I'm mistaken.
> 
> You could write a function to format the interval.  For example,
> with PL/pgSQL you could use EXTRACT(epoch FROM interval_value) to
> convert the interval to a number of seconds; convert that to hours,
> minutes, and seconds; and use TO_CHAR to format the return value.
> 

Something like this, perhaps:

create or replace function interval_to_hms(interval)
returns text
language 'plpgsql'
as 'declare
     interval_sec integer;
     sec integer;
     min integer;
     hr integer;
begin
     select into interval_sec extract (epoch from $1);
     sec  :=  interval_sec % 60;
     min  := (interval_sec/60)%60;
     hr   := (interval_sec/3600);

     return hr || '':'' || to_char(min, ''FM00'') || '':'' || 
to_char(sec, ''FM00'');
end;';


-- 
Edmund Bacon <ebacon@onesystem.com>