Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 7819C9F9A1A for ; Sun, 16 Sep 2007 08:27:26 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 80309-03 for ; Sun, 16 Sep 2007 08:27:24 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from ns1.officenet.no (vpn.officenet.no [193.212.174.13]) by postgresql.org (Postfix) with ESMTP id 979119F99A8 for ; Sun, 16 Sep 2007 08:27:23 -0300 (ADT) Received: from [84.48.119.79] (helo=andreak-laptop.local) by ns1.officenet.no with esmtpsa (TLS-1.0:DHE_RSA_AES_256_CBC_SHA1:32) (Exim 4.63) (envelope-from ) id 1IWsHV-0003Oz-TB; Sun, 16 Sep 2007 13:27:21 +0200 From: Andreas Joseph Krogh Organization: OfficeNet AS To: pgsql-sql@postgresql.org Subject: Re: Format intervall as hours/minutes etc Date: Sun, 16 Sep 2007 13:27:16 +0200 User-Agent: KMail/1.9.6 Cc: Andreas Kretschmer References: <200709161300.53257.andreak@officenet.no> <20070916111427.GA13886@KanotixBox> In-Reply-To: <20070916111427.GA13886@KanotixBox> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200709161327.16989.andreak@officenet.no> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/117 X-Sequence-Number: 29330 On Sunday 16 September 2007 13:14:27 Andreas Kretschmer wrote: > Andreas Joseph Krogh schrieb: > > Hi all. Any hint on how to format this interval as number of hour/secon= ds > > etc? select age('2007-09-22 17:00'::timestamp, '2000-02-20 > > 18:00'::timestamp); age > > ------------------------------- > > 7 years 7 mons 1 day 23:00:00 > > You can use extract(epoch, from ...) like this: > > test=3D*# select extract(epoch from '2007-09-22 17:00'::timestamp) - extr= act > (epoch from '2000-02-20 18:00'::timestamp); ?column? > ----------- > 239407200 > (1 row) > > Now you can calculate the hours and so on. Yes, this works fine for dates >=3D 1970, but I'm looking for a more genera= l=20 solution which takes an arbitrary interval as input. The reason why I'm usi= ng=20 PG to calculate this is 'cause it takes 25/23 hour days, leapyears etc. int= o=20 account when calculating intervals. =2D-=20 Andreas Joseph Krogh Senior Software Developer / Manager =2D-----------------------+---------------------------------------------+ OfficeNet AS | The most difficult thing in the world is to | Karenslyst All=E9 11 | know how to do a thing and to watch | PO. Box 529 Sk=F8yen | somebody else doing it wrong, without | 0214 Oslo | comment. | NORWAY | | Tlf: +47 24 15 38 90 | | =46ax: +47 24 15 38 91 | | Mobile: +47 909 56 963 | | =2D-----------------------+---------------------------------------------+