Re: timestamp without timezone to have timezone

On 11/06/2016 06:11 AM, Benjamin Adams wrote:
> I have a server that has a column timestamp without timezone.
>
> Is the time still saved?

Yes the timestamp is always saved. What that timestamp is differs:

test[5432]=# create table ts_tsz_test(fld_1 timestamp, fld_2 timestamp with time zone);
                                     
CREATE TABLE

             
test[5432]=# insert into ts_tsz_test values (now(), now());
INSERT 0 1

test[5432]=# select * from ts_tsz_test ;
           fld_1            |             fld_2
----------------------------+-------------------------------
 2016-11-06 07:52:01.053218 | 2016-11-06 07:52:01.053218-08


As you can see the timestamp field is a naive value, it has no concept of timezone and
assumes local time. The timestamp with time zone is time zone aware, in this case displaying
as my local time also with the appropriate offset. The reason being that timestamp with time
zone is stored as UTC and converted on display. To learn more see the below:

https://www.postgresql.org/docs/9.5/static/datatype-datetime.html
8.5.1.3. Time Stamps

> if I select column with timestamp it will show server timestamp with
> timezone.

Correct.

>
> But If I move the data from EST to Central will the timestamp with
> timezone be correct?

Are you moving the data or the server or both?

In other words can you be more specific about what moving the data means?

If you are not moving the server(eg retaining the TimeZome config) then the timestamp(w/o tz)
will be displaying in EST not Central. Postgres has no internal way of knowing
what the timestamp(w/o tz) data values are anchored to. This leads to another question.

Did all the current values originate from EST?

> Or will it just not make the adjustment?

See above.
>
> Thanks
> Ben


--
Adrian Klaver
adrian.klaver@aklaver.com