Re: definative way to place secs from epoc into timestamp column - Mailing list pgsql-sql

Bret Hughes <bhughes@elevating.com> writes:
> I give up.  I have STFW and STFM and still do not feel like I have a
> good way to update/insert into a timestamp w/o TZ column with an integer
> representing seconds from epoch.

The docs say:
 Here is how you can convert an epoch value back to a time stamp: 
    SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second';

If you want a timestamp w/o time zone then the right thing depends on
what you think the reference epoch is.  If you do
    SELECT TIMESTAMP WITHOUT TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second';

then what you will get is the correct equivalent of the Unix timestamp
in GMT time.  If you do the first calculation and then cast to timestamp
w/o time zone then what you will get is a correct equivalent in your
TimeZone setting.  For instance

regression=# show timezone;TimeZone
----------EST5EDT
(1 row)

regression=# SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second';       ?column?
------------------------2001-02-16 23:38:40-05
(1 row)

regression=# SELECT TIMESTAMP WITHOUT TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second';     ?column?
---------------------2001-02-17 04:38:40
(1 row)

regression=# SELECT (TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second')::timestamp without time zone;
  timestamp
 
---------------------2001-02-16 23:38:40
(1 row)
        regards, tom lane