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 <[EMAIL PROTECTED]>
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
