Message-ID: <45CA0D74.7010101@presinet.com>
Date: Wed, 07 Feb 2007 09:33:40 -0800
From: Bricklen Anderson <banderson@presinet.com>
User-Agent: Thunderbird 1.5.0.9 (X11/20070103)
MIME-Version: 1.0
To: aarni@kymi.com
CC: pgsql-sql@postgresql.org
Subject: Re: interval as hours or minutes ?
References: <200702071903.35126.aarni@kymi.com>
In-Reply-To: <200702071903.35126.aarni@kymi.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

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.