Andreas Joseph Krogh <andreak@officenet.no> 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 {