Bret Hughes <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
