Re: Interval Formatting -> Convert to timestamp

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@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------