Re: Converting epoch to timestamp

Michael Glaesemann <grzm@myrealbox.com> 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