On Sunday 16 September 2007 17:41:56 Tom Lane wrote: > Andreas Joseph Krogh <[EMAIL PROTECTED]> 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 ...
Ok. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager ------------------------+---------------------------------------------+ OfficeNet AS | The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment. | NORWAY | | Tlf: +47 24 15 38 90 | | Fax: +47 24 15 38 91 | | Mobile: +47 909 56 963 | | ------------------------+---------------------------------------------+ ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
