Home > mailing lists

Thread: timestamp without timezone to have timezone

timestamp without timezone to have timezone

From

Benjamin Adams

Date:

06 November 2016, 17:12:06

I have a server that has a column timestamp without timezone.

Is the time still saved?

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

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

Or will it just not make the adjustment?

Thanks

Ben

Re: timestamp without timezone to have timezone

From

Melvin Davidson

Date:

06 November 2016, 18:11:03

On Sun, Nov 6, 2016 at 9:11 AM, Benjamin Adams <benjamindadams@gmail.com> wrote:

I have a server that has a column timestamp without timezone.

Is the time still saved?
if I select column with timestamp it will show server timestamp with timezone.

But If I move the data from EST to Central will the timestamp with timezone be correct?
Or will it just not make the adjustment?

Thanks
Ben

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

The correct way to do that is to use the AT TIME ZONE function.

https://www.postgresql.org/docs/9.4/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT

eg:

postgres=> SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'EST' as Eastern,
postgres->        TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'CST' as Central,
postgres->        TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST' as Mountain,
postgres->        TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'PST' as Pacific;
        eastern         |        central         |        mountain        |        pacific
------------------------+------------------------+------------------------+------------------------
 2001-02-16 20:38:40-05 | 2001-02-16 21:38:40-05 | 2001-02-16 22:38:40-05 | 2001-02-16 23:38:40-05
(1 row)

Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Re: timestamp without timezone to have timezone

From

Adrian Klaver

Date:

06 November 2016, 19:08:04

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

Re: timestamp without timezone to have timezone

From

Steve Crawford

Date:

06 November 2016, 21:16:48

On Sun, Nov 6, 2016 at 7:10 AM, Melvin Davidson <melvin6925@gmail.com> wrote:
>
>
>
> On Sun, Nov 6, 2016 at 9:11 AM, Benjamin Adams <benjamindadams@gmail.com> wrote:
>>
>> I have a server that has a column timestamp without timezone.
>>
>> Is the time still saved?
>> if I select column with timestamp it will show server timestamp with timezone.
>>
>> But If I move the data from EST to Central will the timestamp with timezone be correct?
>> Or will it just not make the adjustment?
>>
>> Thanks
>> Ben
>
>
> >But If I move the data from EST to Central will the timestamp with timezone be correct?
>
> The correct way to do that is to use the AT TIME ZONE function.
>
> https://www.postgresql.org/docs/9.4/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT
>
> eg:
>
> postgres=> SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'EST' as Eastern,
> postgres-> TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'CST' as Central,
> postgres-> TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST' as Mountain,
> postgres-> TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'PST' as Pacific;
> eastern | central | mountain | pacific
> ------------------------+------------------------+------------------------+------------------------
> 2001-02-16 20:38:40-05 | 2001-02-16 21:38:40-05 | 2001-02-16 22:38:40-05 | 2001-02-16 23:38:40-05
> (1 row)

The question does not completely make sense and Melvin's answer is headed in the right direction but there are a number of subtleties and complications.

First, let me rephrase to make sure I understand the question. You have some table(s) with column(s) of type timestamp without time zone. You currently view the data from the perspective of US/Eastern (probably not actually EST - more comments on the difference between offsets and zones below) and want to know what happens if you view it from the perspective of US/Central.

The short answer is that nothing will change. I'm in US/Pacific by default:

steve=> create temporary table foo (bar timestamp without time zone);
CREATE TABLE
steve=> insert into foo values (now());
INSERT 0 1

steve=> select bar from foo;
bar
----------------------------
2016-11-06 08:57:06.808096

steve=> set timezone to 'US/Eastern';

steve=> select bar from foo;
bar
----------------------------
2016-11-06 08:57:06.808096

steve=> set timezone to 'US/Central';

steve=> select bar from foo;
bar
----------------------------
2016-11-06 08:57:06.808096

But if you do any sort of operation for which the timestamp alone is not sufficient thus time zone information is required, PostgreSQL will convert/calculate based on the current time zone setting.

Starting back at Pacific time, if I look at various other time zones I get:
steve=> select
bar at time zone 'US/Pacific',
bar at time zone 'US/Central',
bar at time zone 'US/Eastern'
from
foo;
-[ RECORD 1 ]---------------------------
timezone | 2016-11-06 08:57:06.808096-08
timezone | 2016-11-06 06:57:06.808096-08
timezone | 2016-11-06 05:57:06.808096-08

But if my client is set to Eastern I get:

steve=> set time zone 'US/Eastern';

steve=> select
bar at time zone 'US/Pacific',
bar at time zone 'US/Central',
bar at time zone 'US/Eastern'
from
foo;
-[ RECORD 1 ]---------------------------
timezone | 2016-11-06 11:57:06.808096-05
timezone | 2016-11-06 09:57:06.808096-05
timezone | 2016-11-06 08:57:06.808096-05

If you alter the table and change the data type to timestamp with time zone (which to my mind is a bad name that we are stuck with - it should be thought of as a "point in time" that can be displayed in any local time zone) the data will be converted as above based on the current time zone setting.

Another "gotcha": "EST" is an *offset* from UTC - specifically, it is 5-hours behind UTC. "US/Eastern", or one of the equivalent full names for that zone (select * from pg_timezone_names;), is a time *zone*. Time zones incorporate the various spring-forward/fall-back offset rules as they have changed through history. Today is a convenient day for demonstrating. I changed the table to have two columns, bar1 and bar2. bar2 has the timestamp we used before and bar1 is the same timestamp but a day earlier. This is what you will get if you display the values and the difference between them in a zone-unaware way:

steve=> select bar1, bar2, bar2-bar1 from foo;
-[ RECORD 1 ]------------------------
bar1 | 2016-11-05 08:57:06.808096
bar2 | 2016-11-06 08:57:06.808096
?column? | 1 day

Now, let's change the data types (I'm still set to US/Eastern):

steve=> alter table foo alter column bar1 type timestamp with time zone;

steve=> alter table foo alter column bar2 type timestamp with time zone;

steve=> select bar1, bar2, bar2-bar1 from foo;
-[ RECORD 1 ]---------------------------
bar1 | 2016-11-05 08:57:06.808096-04
bar2 | 2016-11-06 08:57:06.808096-05
?column? | 1 day 01:00:00

Note that timestamp was converted to a timestamp with time zone using the rules appropriate for the given dates and currently set time zone so today is an offset of -05 and yesterday is -04. The difference, therefore, is 1-day plus 1-hour.

Note, also, that you can convert to "zones" that don't actually exist. In the example below I asked for the data to be represented in EST (correct for today but never changes offsets), US/Eastern (automatically handles changing offsets) and EDT (represents -04 hours even though that doesn't make sense for today). Two things to note. First, PostgreSQL is converting from a timestamp to a timestamp with time zone according to the rule provided. Second, it is *displaying* the resulting timestamp with time zone in the currently selected zone (US/Eastern) so while the conversion for EST was done using -04, the display for all values is -05 which is appropriate for the currently set time zone.

steve=> select
bar at time zone 'EST' as "EST",
bar at time zone 'EDT' as "EDT",
bar at time zone 'US/Eastern' as "US/Eastern"
from
foo;
-[ RECORD 1 ]-----------------------------
EST | 2016-11-06 08:57:06.808096-05
EDT | 2016-11-06 07:57:06.808096-05
US/Eastern | 2016-11-06 08:57:06.808096-05

Exam time. Predict the output of the following without running it:

steve=> set time zone 'US/Central';

steve=> select

('2016-11-06 10:00 US/Pacific'::timestamptz at time zone 'US/Eastern') at time zone 'US/Mountain',

('2016-11-06 00:30 US/Pacific'::timestamptz at time zone 'US/Eastern') at time zone 'US/Mountain';

Date/time rules and manipulation can be tricky. I recommend playing with the available types, settings, operators and functions to see how they work and then setting up a test version of your database to run tests on your database updates and any associated code before attempting anything on your live database.

Cheers,
Steve

Re: timestamp without timezone to have timezone

From

Adrian Klaver

Date:

06 November 2016, 23:29:53

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.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.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'
fromts_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'
dependingon 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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>


--
Adrian Klaver
adrian.klaver@aklaver.com