Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id BB00F9FB25E for ; Thu, 8 Feb 2007 06:06:40 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 43864-07 for ; Thu, 8 Feb 2007 06:06:17 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from interscan.indicator.com (mail.indicator.be [193.121.199.3]) by postgresql.org (Postfix) with ESMTP id B5BAA9FA312 for ; Thu, 8 Feb 2007 06:06:17 -0400 (AST) Received: from webaccess.indicator.be ([10.100.1.7]) by interscan.indicator.com with InterScan Message Security Suite; Thu, 08 Feb 2007 10:54:23 +0100 Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise; Thu, 08 Feb 2007 11:06:15 +0100 Message-Id: <45CB041D.A3DD.0030.0@indicator.be> X-Mailer: Novell GroupWise Internet Agent 7.0.1 Date: Thu, 08 Feb 2007 11:06:06 +0100 From: "Bart Degryse" To: Subject: Re: interval as hours or minutes ? References: <200702071903.35126.aarni@kymi.com><45CA07D5.9647.29301B0@postgr es.jancm.org><200702081201.18645.aarni@kymi.com> In-Reply-To: <200702081201.18645.aarni@kymi.com> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__Part0F2B8D1E.0__=" X-imss-version: 2.046 X-imss-result: Passed X-imss-approveListMatch: *@indicator.be X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.136 tagged_above=0 required=5 tests=AWL, BAYES_50, FORGED_RCVD_HELO, HTML_30_40, HTML_MESSAGE, UNPARSEABLE_RELAY X-Spam-Level: X-Archive-Number: 200702/80 X-Sequence-Number: 27414 --=__Part0F2B8D1E.0__= Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit Use trunc instead of round. Also take a look at ceil and floor functions >>> Aarni Ruuhimäki 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 --=__Part0F2B8D1E.0__= Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Description: HTML

Use trunc instead of round.

Also take a look at ceil and floor functions

>>> = Aarni Ruuhim=E4ki <aarni@kymi.com> 2007-02-08 11:01 >>>
O= n 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 =3D 10 AND start_date_time >=3D '2007-01-01= ' AND
stop_date_time <=3D '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 =3D 10 AND start_date_time >=3D '2007-01-01= ' AND
stop_date_time <=3D '2007-02-28'),'minutes') as minutes;
&n= bsp;        minutes
-----------------= ---------
3728.73333333333 minutes
(1 row)

select round(extrac= t(epoch from (SELECT SUM(stop_date_time - start_date_time)
FROM = work_times WHERE user_id =3D 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=E4ki

---------------------------(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
    &= nbsp; match

--=__Part0F2B8D1E.0__=--