Re: interval as hours or minutes ?

Aarni Ruuhimäki wrote:
> Hi all,
> 
> Could anyone please tell an easy way to get total hours or minutes from an 
> interval ?
> 
> SELECT SUM(stop_date_time - start_date_time) AS tot_time FROM work_times WHERE 
> user_id = 1;
>     tot_time
> -----------------
>  2 days 14:08:44
> 
> I'd like to have this like ... AS tot_hours ...
>     tot_hours
> -----------------
> 62
> 
> and ... AS tot_minutes ...
>     tot_minutes
> -----------------
> 3728
> 
> Maybe even ... AS tot_hours_minutes_seconds
>     tot_hours_minutes_seconds
> -----------------
> 62:08:44
> 
> 
> start_date_time and stop_date_time are stored as timestamp without time zone, 
> using Pg 8.1.5 on CentOs 4.4
> 
> ???
> 
> Thanks,
> 

I have been using the following function (watch for line wrap)

CREATE OR REPLACE function convert_interval(interval,text) returns text 
as $$
declare    retval TEXT;    my_interval INTERVAL := $1;    my_type TEXT := $2;    qry TEXT;
begin    if my_type ~* 'hour' then        select into retval extract(epoch from 
my_interval::interval)/3600 || ' hours';    elsif my_type ~* 'min' then        select into retval extract(epoch from
my_interval::interval)/60
 
|| ' minutes';    elsif my_type ~* 'day' then        select into retval extract(epoch from 
my_interval::interval)/86400 || ' days';    elsif my_type ~* 'sec' then        select into retval extract(epoch from
my_interval::interval)|| 
 
' seconds';    end if;
RETURN retval;
end;
$$ language plpgsql strict immutable;

pqsl=# select convert_interval(now() - (now()-interval '1 day 4 hours 6 
minutes'),'minutes') as minutes;   minutes
-------------- 1686 minutes

There may be something built-in now, but I haven't looked recently.