Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 593459FA4F0 for ; Thu, 8 Feb 2007 07:18:49 -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 54572-07 for ; Thu, 8 Feb 2007 07:17:59 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from smtp10.kymp.net (smtp10.kymp.net [80.248.96.136]) by postgresql.org (Postfix) with ESMTP id AB0D59FA30D for ; Thu, 8 Feb 2007 07:17:59 -0400 (AST) Received: from kymifedora (static-241-66.kymp.net [213.130.241.66]) by smtp10.kymp.net (8.13.6/8.13.3) with SMTP id l18BHu2w008769 for ; Thu, 8 Feb 2007 13:17:56 +0200 From: Aarni =?iso-8859-1?q?Ruuhim=E4ki?= Reply-To: aarni@kymi.com Organization: KYMI.com To: pgsql-sql@postgresql.org Subject: Re: interval as hours or minutes ? Date: Thu, 8 Feb 2007 13:17:57 +0200 User-Agent: KMail/1.6.2 References: <200702071903.35126.aarni@kymi.com> <200702081201.18645.aarni@kymi.com> <45CB041D.A3DD.0030.0@indicator.be> In-Reply-To: <45CB041D.A3DD.0030.0@indicator.be> MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message-Id: <200702081317.57255.aarni@kymi.com> X-Virus-Scanned: by amavisd-new X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.031 tagged_above=0 required=5 tests=AWL, BAYES_50 X-Spam-Level: X-Archive-Number: 200702/81 X-Sequence-Number: 27415 Ahh, =46orgot 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=E4ki 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 =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; > 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 =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,