To: Bret Hughes <bhughes@elevating.com>
Cc: postgresql sql list <pgsql-sql@postgresql.org>
Subject: Re: definative way to place secs from epoc into timestamp column 
In-reply-to: <1109780719.28180.86.camel@bretsony> 
References: <1109780719.28180.86.camel@bretsony>
Comments: In-reply-to Bret Hughes <bhughes@elevating.com>
	message dated "02 Mar 2005 10:25:17 -0600"
Date: Wed, 02 Mar 2005 14:52:06 -0500
Message-ID: <13089.1109793126@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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