Re: Format intervall as hours/minutes etc

[Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>]

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
  	{