Re: Format intervall as hours/minutes etc

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     {