Re: timestamp without timezone to have timezone - Mailing list pgsql-general

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: