On Sun, Nov 6, 2016 at 7:10 AM, Melvin Davidson <melvin6925@xxxxxxxxx> wrote:
>
>
>
> On Sun, Nov 6, 2016 at 9:11 AM, Benjamin Adams <benjamindadams@xxxxxxxxx> 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.
steve=> select
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
>
>
>
> On Sun, Nov 6, 2016 at 9:11 AM, Benjamin Adams <benjamindadams@xxxxxxxxx> 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
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';
Cheers,
Steve
