Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id B9D369FA4F0 for ; Thu, 8 Feb 2007 06:01:34 -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 42871-04 for ; Thu, 8 Feb 2007 06:01:22 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from smtp20.kymp.net (smtp20.kymp.net [80.248.96.137]) by postgresql.org (Postfix) with ESMTP id 3E02D9FA30D for ; Thu, 8 Feb 2007 06:01:22 -0400 (AST) Received: from kymifedora (static-241-66.kymp.net [213.130.241.66]) by smtp20.kymp.net (8.13.3/8.13.3) with SMTP id l18A1I0W006482; Thu, 8 Feb 2007 12:01:19 +0200 From: Aarni =?iso-8859-1?q?Ruuhim=E4ki?= Reply-To: aarni@kymi.com Organization: KYMI.com To: "Jan Muszynski" Subject: Re: interval as hours or minutes ? Date: Thu, 8 Feb 2007 12:01:18 +0200 User-Agent: KMail/1.6.2 References: <200702071903.35126.aarni@kymi.com> <45CA07D5.9647.29301B0@postgres.jancm.org> In-Reply-To: <45CA07D5.9647.29301B0@postgres.jancm.org> Cc: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message-Id: <200702081201.18645.aarni@kymi.com> X-Virus-Scanned: by amavisd-new X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.039 tagged_above=0 required=5 tests=AWL, BAYES_50 X-Spam-Level: X-Archive-Number: 200702/79 X-Sequence-Number: 27413 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; > > =A0 =A0 =A0minutes > ------------------ > =A03083.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; > > =A0minutes > --------- > =A0 =A0 3084 > (1 row) Hi Guys, Charming ! =46urher still, I would only want full minutes. select extract(epoch from (SELECT SUM(stop_date_time - start_date_time) FRO= M=20 work_times WHERE user_id =3D 10 AND start_date_time >=3D '2007-01-01' AND=20 stop_date_time <=3D '2007-02-28')/60) as mins; mins =2D------------ 3728.733333 (1 row) select convert_interval((SELECT SUM(stop_date_time - start_date_time) FROM= =20 work_times WHERE user_id =3D 10 AND start_date_time >=3D '2007-01-01' AND=20 stop_date_time <=3D '2007-02-28'),'minutes') as minutes; minutes =2D------------------------- 3728.73333333333 minutes (1 row) select round(extract(epoch from (SELECT SUM(stop_date_time - start_date_tim= e)=20 =46ROM work_times WHERE user_id =3D 10))/60) as mins; mins =2D----- 3729 (1 row) So instead of rounding up to 3729 the result would have to be 'stripped' to= =20 3728 ? Thanks, =2D-=20 Aarni Ruuhim=E4ki