Thread: interval as hours or minutes ?

interval as hours or minutes ?

From

Aarni Ruuhimäki

Date:

07 February 2007, 16:22:34

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,

--
Aarni Ruuhimäki

Re: interval as hours or minutes ?

From

Bricklen Anderson

Date:

07 February 2007, 16:33:52

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.

Re: interval as hours or minutes ?

From

"A. Kretschmer"

Date:

07 February 2007, 16:44:43

am  Wed, dem 07.02.2007, um 19:03:35 +0200 mailte Aarni Ruuhimäki folgendes:
> 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;

Perhaps. You can, for instance, with extract(epoch from stop_date_time)
retrieve the number of seconds and work with this. 


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: interval as hours or minutes ?

From

Bruno Wolff III

Date:

07 February 2007, 20:26:10

On Wed, Feb 07, 2007 at 19:03:35 +0200, Aarni Ruuhimäki <aarni@kymi.com> wrote:
> 
> Could anyone please tell an easy way to get total hours or minutes from an 
> interval ?

Extract the epoch from the interval and divide by the number of seconds
in the period of time that applies and apply appropiate rounding.

Re: interval as hours or minutes ?

From

"Jan Muszynski"

Date:

07 February 2007, 21:10:23

On 7 Feb 2007 at 19:03, 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


select extract(epoch from (timestamp '2007-02-07 16:24:00' - timestamp '2007-02-05
13:00:00')) as num_seconds;
num_seconds
------------     185040
(1 row)

select (extract(epoch from (timestamp '2007-02-07 16:24:00' - timestamp '2007-02-05
13:00:00')) * interval '1 second') as hours_minutes_seconds;
hours_minutes_seconds
-----------------------51:24:00
(1 row)

select extract(epoch from (timestamp '2007-02-07 16:24:00' - timestamp '2007-02-05
13:00:01'))/60 as minutes;
    minutes
------------------3083.98333333333
(1 row)

select round(extract(epoch from (timestamp '2007-02-07 16:24:00' - timestamp '2007-02-05
13:00:01'))/60) as minutes;
minutes
---------   3084
(1 row)


select round(extract(epoch from (timestamp '2007-02-07 16:24:00' - timestamp '2007-02-05
13:00:01'))/360) as hours;
hours
-------  514
(1 row)

Re: interval as hours or minutes ?

From

Aarni Ruuhimäki

Date:

08 February 2007, 09:01:35

On Thursday 08 February 2007 00:09, you wrote:
> select extract(epoch from (timestamp '2007-02-07 16:24:00' - timestamp
> '2007-02-05 13:00:01'))/60 as minutes;
>
>      minutes
> ------------------
>  3083.98333333333
> (1 row)
>
> select round(extract(epoch from (timestamp '2007-02-07 16:24:00' -
> timestamp '2007-02-05 13:00:01'))/60) as minutes;
>
>  minutes
> ---------
>     3084
> (1 row)

Hi Guys,

Charming !

Furher still, I would only want full minutes.

select extract(epoch from (SELECT SUM(stop_date_time - start_date_time) FROM
work_times WHERE user_id = 10 AND start_date_time >= '2007-01-01' AND
stop_date_time <= '2007-02-28')/60) as mins;   mins
-------------3728.733333
(1 row)

select convert_interval((SELECT SUM(stop_date_time - start_date_time) FROM
work_times WHERE user_id = 10 AND start_date_time >= '2007-01-01' AND
stop_date_time <= '2007-02-28'),'minutes') as minutes;        minutes
--------------------------3728.73333333333 minutes
(1 row)

select round(extract(epoch from (SELECT SUM(stop_date_time - start_date_time)
FROM work_times WHERE user_id = 10))/60) as mins;mins
------3729
(1 row)

So instead of rounding up to 3729 the result would have to be 'stripped' to
3728 ?

Thanks,


--
Aarni Ruuhimäki

Re: interval as hours or minutes ?

From

"Bart Degryse"

Date:

08 February 2007, 09:06:41

Use trunc instead of round.

Also take a look at ceil and floor functions

>>> Aarni Ruuhimäki <aarni@kymi.com> 2007-02-08 11:01 >>>
On Thursday 08 February 2007 00:09, you wrote:
> select extract(epoch from (timestamp '2007-02-07 16:24:00' - timestamp
> '2007-02-05 13:00:01'))/60 as minutes;
>
>      minutes
> ------------------
> 3083.98333333333
> (1 row)
>
> select round(extract(epoch from (timestamp '2007-02-07 16:24:00' -
> timestamp '2007-02-05 13:00:01'))/60) as minutes;
>
> minutes
> ---------
>     3084
> (1 row)

Hi Guys,

Charming !

Furher still, I would only want full minutes.

select extract(epoch from (SELECT SUM(stop_date_time - start_date_time) FROM
work_times WHERE user_id = 10 AND start_date_time >= '2007-01-01' AND
stop_date_time <= '2007-02-28')/60) as mins;
    mins
-------------
3728.733333
(1 row)

select convert_interval((SELECT SUM(stop_date_time - start_date_time) FROM
work_times WHERE user_id = 10 AND start_date_time >= '2007-01-01' AND
stop_date_time <= '2007-02-28'),'minutes') as minutes;
         minutes
--------------------------
3728.73333333333 minutes
(1 row)

select round(extract(epoch from (SELECT SUM(stop_date_time - start_date_time)
FROM work_times WHERE user_id = 10))/60) as mins;
mins
------
3729
(1 row)

So instead of rounding up to 3729 the result would have to be 'stripped' to
3728 ?

Thanks,

--
Aarni Ruuhimäki

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

Re: interval as hours or minutes ?

From

Aarni Ruuhimäki

Date:

08 February 2007, 10:18:50

Ahh,

Forgot about trunc() in the midst of all this ...

Thank you guys again !

Aarni

On Thursday 08 February 2007 12:06, Bart Degryse wrote:
> Use trunc instead of round.
> Also take a look at ceil and floor functions
>
> >>> Aarni Ruuhimäki <aarni@kymi.com> 2007-02-08 11:01 >>>
>
> On Thursday 08 February 2007 00:09, you wrote:
> > select extract(epoch from (timestamp '2007-02-07 16:24:00' -
>
> timestamp
>
> > '2007-02-05 13:00:01'))/60 as minutes;
> >
> >      minutes
> > ------------------
> >  3083.98333333333
> > (1 row)
> >
> > select round(extract(epoch from (timestamp '2007-02-07 16:24:00' -
> > timestamp '2007-02-05 13:00:01'))/60) as minutes;
> >
> >  minutes
> > ---------
> >     3084
> > (1 row)
>
> Hi Guys,
>
> Charming !
>
> Furher still, I would only want full minutes.
>
> select extract(epoch from (SELECT SUM(stop_date_time - start_date_time)
> FROM
> work_times WHERE user_id = 10 AND start_date_time >= '2007-01-01' AND
> stop_date_time <= '2007-02-28')/60) as mins;
>     mins
> -------------
> 3728.733333
> (1 row)
>
> select convert_interval((SELECT SUM(stop_date_time - start_date_time)
> FROM
> work_times WHERE user_id = 10 AND start_date_time >= '2007-01-01' AND
> stop_date_time <= '2007-02-28'),'minutes') as minutes;
>          minutes
> --------------------------
> 3728.73333333333 minutes
> (1 row)
>
> select round(extract(epoch from (SELECT SUM(stop_date_time -
> start_date_time)
> FROM work_times WHERE user_id = 10))/60) as mins;
> mins
> ------
> 3729
> (1 row)
>
> So instead of rounding up to 3729 the result would have to be
> 'stripped' to
> 3728 ?
>
> Thanks,