Michael Glaesemann <[EMAIL PROTECTED]> writes: > create or replace function epoch_to_timestamp( > integer > ) returns timestamp(0) > language sql as ' > SELECT ''epoch''::timestamp + $1 * ''1 second''::interval; > ';
This is in fact wrong, unless you live in the British Isles: the result will be off by your timezone displacement from UTC. Correct is to use timestamptz not timestamp. As an example: timestamp 1089953023 equates to Fri Jul 16 2004, 00:43:43 EDT according to strftime() on my machine (I live in US Eastern zone which is presently GMT-4). I get regression=# select 'epoch'::timestamp + 1089953023 * '1 second'::interval; ?column? --------------------- 2004-07-16 04:43:43 (1 row) regression=# select 'epoch'::timestamptz + 1089953023 * '1 second'::interval; ?column? ------------------------ 2004-07-16 00:43:43-04 (1 row) The first is not right, the second is ... > I'm wondering if this wouldn't be better as cast rather than explicit > functions. A cast from integer is probably a bad idea, seeing that it will break in 2038. You could make an argument for a cast from double though. The issue to my mind is whether this might be too Unix-centric. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html