On 21/08/2008 10:09, Ow Mun Heng wrote:
> I want to find out if there's a method to change this
>
> select to_char('1 day 09:18:42.37996'::interval,'HH24:MI:SS')
>
> to something like
>
> 24+9(hours) = 33:18:42 instead of returning It as 09:19:42
I had to do something like this recently when adding up the durations of
music CD tracks (stored as INTERVALs), and I wanted the totals returned
in terms of minutes rather than hours:
create or replace function format_duration(theDuration interval)
returns text
as
$$
select
to_char(extract(hour from $1) * 60 + extract(minute from $1), 'FM9,999')
|| to_char(extract(second from $1), '":"FM00');
$$
language sql;
HTH,
Ray.
------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@xxxxxx
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------
