Search Postgresql Archives

Re: timestamp without timezone to have timezone

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux