Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id A62BB9F93A4 for ; Wed, 7 Feb 2007 18:10:22 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 90250-01 for ; Wed, 7 Feb 2007 18:10:08 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from relay03.roc.ny.frontiernet.net (relay03.roc.ny.frontiernet.net [66.133.182.166]) by postgresql.org (Postfix) with ESMTP id 9E3C59FA15F for ; Wed, 7 Feb 2007 18:10:11 -0400 (AST) X-Virus-Scanned: by amavisd-new-2.4.1 at filter04.roc.ny.frontiernet.net X-Trace: 53616c7465645f5f3a15d0748d3b19631e75746a15479ca33c747c8590f1c9f10f05df0642f594a83931fd5f38956e5681b791bccb4b0cd601522bfd4bff67d3a598b8bbb08b75db7134ef3938358bc8a94f58135d1201f713dc18a255f0d954 Received: from wingfoot.jancm.org (74-36-20-163.dsl1.mon.ny.frontiernet.net [74.36.20.163]) by relay03.roc.ny.frontiernet.net (Postfix) with ESMTP id 82C13C5A1; Wed, 7 Feb 2007 22:10:03 +0000 (UTC) Received: from Spooler by wingfoot.jancm.org (Mercury/32 v4.51) ID MO00004B; 7 Feb 2007 17:10:04 -0500 Received: from spooler by jancm.org (Mercury/32 v4.51); 7 Feb 2007 17:09:54 -0500 X-CLAMWALL: Passed through antiviral test by ClamWall 1.1.3 on jancm.org (113) Received: from local.jancm.org (10.54.11.10) by SMTP.JANCM.ORG (Mercury/32 v4.51) with ESMTP ID MG00004A (Using SSL/TLS, 3DES, CBC mode, keysize 192 bits) ; 7 Feb 2007 17:09:42 -0500 From: "Jan Muszynski" Organization: Chaotic at Best To: aarni@kymi.com Date: Wed, 07 Feb 2007 17:09:41 -0500 MIME-Version: 1.0 Subject: Re: interval as hours or minutes ? CC: pgsql-sql@postgresql.org Message-ID: <45CA07D5.9647.29301B0@postgres.jancm.org> In-reply-to: <200702071903.35126.aarni@kymi.com> References: <200702071903.35126.aarni@kymi.com> X-mailer: Pegasus Mail for Windows (4.41) Content-type: text/plain; charset=UTF-8 Content-transfer-encoding: Quoted-printable Content-description: Mail message body X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/68 X-Sequence-Number: 27402 On 7 Feb 2007 at 19:03, Aarni Ruuhim=C3=A4ki 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 =3D 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 '20= 07-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 '2= 007-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 '20= 07-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' - timesta= mp '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' - timesta= mp '2007-02-05 13:00:01'))/360) as hours; hours ------- 514 (1 row)