From: Bricklen Anderson <banderson@presinet.com>
To: aarni@kymi.com
Cc: pgsql-sql@postgresql.org
Subject: Re: interval as hours or minutes ?
Date: Wed, 07 Feb 2007 09:33:40 -0800
Message-ID: <45CA0D74.7010101@presinet.com> (raw)
In-Reply-To: <200702071903.35126.aarni@kymi.com>
References: <200702071903.35126.aarni@kymi.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: banderson@presinet.com, aarni@kymi.com
Subject: Re: interval as hours or minutes ?
In-Reply-To: <45CA0D74.7010101@presinet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox