Re: Format intervall as hours/minutes etc

Andreas Joseph Krogh <andreak(at)officenet(dot)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
{