Re: timestamp without timezone to have timezone

On Sun, Nov 6, 2016 at 7:10 AM, Melvin Davidson <melvin6925(at)gmail(dot)com>
wrote:
>
>
>
> On Sun, Nov 6, 2016 at 9:11 AM, Benjamin Adams <benjamindadams(at)gmail(dot)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