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.