Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id AC34F9F971A for ; Sun, 16 Sep 2007 12:42:06 -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 19559-03 for ; Sun, 16 Sep 2007 12:42:03 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 961CC9F9700 for ; Sun, 16 Sep 2007 12:42:03 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l8GFfu34006630; Sun, 16 Sep 2007 11:41:56 -0400 (EDT) To: Andreas Joseph Krogh cc: pgsql-sql@postgresql.org, Andreas Kretschmer Subject: Re: Format intervall as hours/minutes etc In-reply-to: <200709161327.16989.andreak@officenet.no> References: <200709161300.53257.andreak@officenet.no> <20070916111427.GA13886@KanotixBox> <200709161327.16989.andreak@officenet.no> Comments: In-reply-to Andreas Joseph Krogh message dated "Sun, 16 Sep 2007 13:27:16 +0200" Date: Sun, 16 Sep 2007 11:41:56 -0400 Message-ID: <6629.1189957316@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/118 X-Sequence-Number: 29331 Andreas Joseph Krogh writes: > On Sunday 16 September 2007 13:14:27 Andreas Kretschmer wrote: >> You can use extract(epoch, from ...) like this: >> >> test=*# select extract(epoch from '2007-09-22 17:00'::timestamp) - extract >> (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 >= 1970, but I'm looking for a more general There's no particular restriction to dates after 1970 there. > solution which takes an arbitrary interval as input. Well, you could subtract the two timestamps and then "extract(epoch ...)" the resulting interval, but I think you'll get the very same answer. [ pokes at it ... ] Hm, we seem to have an overflow problem in the interval-to-epoch code for intervals exceeding 60-some years: regression=# select extract(epoch from '2007-09-22 17:00'::timestamp - '1940-02-20 18:00'::timestamp); date_part ------------ 2132866800 (1 row) regression=# select extract(epoch from '2007-09-22 17:00'::timestamp - '1930-02-20 18:00'::timestamp); date_part ------------- -1846567696 (1 row) Looks pretty trivial to fix ... regards, tom lane Index: timestamp.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v retrieving revision 1.181 diff -c -r1.181 timestamp.c *** timestamp.c 4 Aug 2007 01:26:54 -0000 1.181 --- timestamp.c 16 Sep 2007 15:33:33 -0000 *************** *** 4395,4403 **** #else result = interval->time; #endif ! result += (DAYS_PER_YEAR * SECS_PER_DAY) * (interval->month / MONTHS_PER_YEAR); result += ((double) DAYS_PER_MONTH * SECS_PER_DAY) * (interval->month % MONTHS_PER_YEAR); ! result += interval->day * SECS_PER_DAY; } else { --- 4395,4403 ---- #else result = interval->time; #endif ! result += ((double) DAYS_PER_YEAR * SECS_PER_DAY) * (interval->month / MONTHS_PER_YEAR); result += ((double) DAYS_PER_MONTH * SECS_PER_DAY) * (interval->month % MONTHS_PER_YEAR); ! result += ((double) SECS_PER_DAY) * interval->day; } else {