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.
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
