Re: timestamp without timezone to have timezone

On 11/06/2016 09:24 AM, Benjamin Adams wrote:

Please Reply to list also.
I have Cced list

> On Nov 6, 2016 11:07 AM, "Adrian Klaver" <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>>
>> 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?
>
> Just moving data. Server will have new local time.

If I am following correctly you will be changing the TimeZone setting from EST to Central, correct?

So for practical purposes both, in that the moved data will have naive timestamp data in
a 'new' timezone from its point of view

>
>>
>> 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?
>
> All current are est. If I do select at UTC. Will data respond with same
> time after moving data?

Well first are the values actually all derived from EST or are they a mix of EST/EDT? I suspect the latter.

As to your question, maybe:

https://www.postgresql.org/docs/9.5/static/datatype-datetime.html#DATATYPE-TIMEZONES

8.5.1.3. Time Stamps

"Conversions between timestamp without time zone and timestamp with time zone normally assume
that the timestamp without time zone value should be taken or given as timezone local time.
A different time zone can be specified for the conversion using AT TIME ZONE."

To continue my previous example and given that this morning was the DST --> Standard Time transition. Also
that I am in Pacific time zones:

test[5432]=# insert into ts_tsz_test values ('2016-11-05 07:52:01.053218' , '2016-11-05 07:52:01.053218');
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
2016-11-05 07:52:01.053218 | 2016-11-05 07:52:01.053218-07

test[5432]=# select fld_1, fld_1 at time zone 'utc', fld_2, fld_2 at time zone 'utc' from ts_tsz_test;
fld_1 | timezone | fld_2 | timezone
----------------------------+-------------------------------+-------------------------------+----------------------------
2016-11-06 07:52:01.053218 | 2016-11-06 00:52:01.053218-07 | 2016-11-06 07:52:01.053218-08 | 2016-11-06 15:52:01.053218
2016-11-05 07:52:01.053218 | 2016-11-05 00:52:01.053218-07 | 2016-11-05 07:52:01.053218-07 | 2016-11-05 14:52:01.053218

test[5432]=# select fld_1, fld_1 at time zone 'America/Los_Angeles', fld_2, fld_2 at time zone 'utc' from ts_tsz_test;
fld_1 | timezone | fld_2 | timezone
----------------------------+-------------------------------+-------------------------------+----------------------------
2016-11-06 07:52:01.053218 | 2016-11-06 07:52:01.053218-08 | 2016-11-06 07:52:01.053218-08 | 2016-11-06 15:52:01.053218
2016-11-05 07:52:01.053218 | 2016-11-05 07:52:01.053218-07 | 2016-11-05 07:52:01.053218-07 | 2016-11-05 14:52:01.053218

test[5432]=# select fld_1, fld_1 at time zone 'America/Los_Angeles' at time zone 'UTC', fld_2, fld_2 at time zone 'utc' from ts_tsz_test;
fld_1 | timezone | fld_2 | timezone
----------------------------+----------------------------+-------------------------------+----------------------------
2016-11-06 07:52:01.053218 | 2016-11-06 15:52:01.053218 | 2016-11-06 07:52:01.053218-08 | 2016-11-06 15:52:01.053218
2016-11-05 07:52:01.053218 | 2016-11-05 14:52:01.053218 | 2016-11-05 07:52:01.053218-07 | 2016-11-05 14:52:01.053218

As Steve also pointed out timestamps without time zone information are tricky
to deal with. So if all your timestamps originated in the Eastern time zone(s) I would test using something like my last
example above but substituting 'America/New_York' for 'America/Los_Angeles' and either 'UTC' or 'America/Chicago' depending on whether
you want the end result to be in UTC or local time. So something like:

select your_date_fld at time zone 'America/New_york' at time zone 'UTC';

or

select your_date_fld at time zone 'America/New_york' at time zone 'America/Chicago';

>
>>
>> > Or will it just not make the adjustment?
>>
>> See above.
>> >
>> > Thanks
>> > Ben
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com