On Sun, Nov 6, 2016 at 7:10 AM, Melvin Davidson <melvin6...@gmail.com> wrote: > > > > On Sun, Nov 6, 2016 at 9:11 AM, Benjamin Adams <benjamindad...@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