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.