Re: definative way to place secs from epoc into timestamp column

Bret Hughes <bhughes(at)elevating(dot)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