Re: interval as hours or minutes ?

From: "Bart Degryse" <Bart(dot)Degryse(at)indicator(dot)be>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: interval as hours or minutes ?
Date: 2007-02-08 10:06:06
Message-ID: 45CB041D.A3DD.0030.0@indicator.be
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Use trunc instead of round.
Also take a look at ceil and floor functions

>>> Aarni Ruuhimäki <aarni(at)kymi(dot)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

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Aarni Ruuhimäki 2007-02-08 11:17:57 Re: interval as hours or minutes ?
Previous Message Aarni Ruuhimäki 2007-02-08 10:01:18 Re: interval as hours or minutes ?