Teradata Timestamp - Part 6 - Time Zone Normalization , AT LOCAL

Inserting in a TIMESTAMP with TIME ZONE column:

When we insert row in the table with TIMESTAMP WITH TIME ZONE column, it is not necessary to specify the time zone as the part of timestamp. The current time zone for the session will be stored with the timestamp with default (thus Time Zone is stored Implicitly).

Say we create a table as below :

CREATE VOLATILE TABLE TEST

 (

 tstamp TIMESTAMP WITH TIME ZONE

 ) ON COMMIT PRESERVE ROWS;

INSERT INTO TEST VALUES (TIMESTAMP '2013-01-01 12:34:56.123456'); --> note that we did not specify the time zone when inserting the timestamp. It will pick up the Timezone from the current setting which is system level 00:00

SET TIME ZONE INTERVAL '05:00' HOUR TO MINUTE ;/* explicit session level setting */

INSERT INTO TEST VALUES (TIMESTAMP '2013-01-01 12:34:56.123457'); --> Here again we don’t specify the time zone when inserting the timestamp. It will pick up the time zone from the current setting which is the 05:00 setting made explicitly for this session

select CAST(tstamp AS CHAR(35)) from test ;

tstamp

2013-01-01 12:34:56.123456+00:00  

2013-01-01 12:34:56.123457+05:00  

From the result we can see that zone is defaulted from the current time zone setting.

When your system uses a TIME ZONE of 00:00 we can think as if your system is located at GMT.

When we change the TIME ZONE we can think that your system moved to a different location which follows the new time zone.

Casting TIMESTAMP with TIME ZONE TO TIMESTAMP column (Time Zone Normalization):

We can cast TIMESTAMP WITH TIME ZONE to TIMESTAMP.

This conversion does not drop the TIME ZONE, instead it incorporates the time zone in the timestamp to give us an absolute value. This process is called as Zone Normalization.

The result of normalization is always relative to the current TimeZone setting. (The current setting could come from System level setting Or user level setting OR session level setting)

Example:

SET TIME ZONE LOCAL; --> Set the time Zone to 00:00

select CAST(tstamp AS timestamp(6)) from test  --> 1/1/2013 12:34:56.123457

Relative to GMT the time is 12:34

SET TIME ZONE INTERVAL '05:00' HOUR TO MINUTE ;/* explicit session level setting */

Here now we change the current time zone to Pakistan's time zone

select CAST(tstamp AS timestamp(6)) from test --> 1/1/2013 17:34:56.123457

Now here we get the relative to Pakistan's timezone. We can see that 5 hours are added to 12:34.

What we are seeing here is that 12:34 in GMT is actually 17:34 is Pakistan.

This is how normalization works.-- Relative to current time zone setting.

Inserting records with explicit time zone.

Earlier we saw that we can insert timestamp fields without writing the time zone explicitly. The system will populate the time zone value from the current time zone setting. The current time zone setting can come from system level setting / user level setting or the session level setting.

However we can also insert records with explicit time zone as shown below.

insert into test values (TIMESTAMP'2013-01-01 12:11:11+06:00');

The Time zone +6:00 is followed by Bangladesh.

We are actually inserting time 12:11:11 in Bangladesh.

Now when we normalize we get the following result:

select CAST(tstamp AS timestamp(6)) from test --> 1/1/2013 06:11:11.000000

We see the time as 06:11:11. What we are seeing currently is the time at GMT ( because my system level setting is 00:00 .i.e GMT) when the time in Bangladesh is 12:11:11

Now I change my session level time zone setting to US CST

SET TIME ZONE INTERVAL -'06:00' HOUR TO MINUTE ;/* explicit session level setting */

And when I see the normalized time zone following is what we get

select CAST(tstamp AS TIMESTAMP(6)) from test ; --> 1/1/2013 00:11:11.000000

Note that the time we are seeing is actually the time in Texas when time in bangladesh is 12:11:11 of the same day.

Now I change the TIME ZONE to Hawaii.

SET TIME ZONE INTERVAL -'11:00' HOUR TO MINUTE ;/* explicit session level setting */

 select CAST(tstamp AS TIMESTAMP(6)) from test ; --> 12/31/2012 19:11:11.000000

Note that when its 12:11:11 of 1st Jan 2013 , its 19:11:11 of 31st Dec 2012 in hawaii.

Note that it actually put us on the previous day.

AT LOCAL option:

AT LOCAL is another technique of normalizing the time zones.

Using AT LOCAL has the same effect as casting we performed earlier.

SET TIME ZONE LOCAL; --> Sets the Time Zone to system level setting which is 00:00 on my system.

select CAST(tstamp AS TIMESTAMP(6)) from test ; --> 1/1/2013 06:11:11.000000

SET TIME ZONE INTERVAL -'11:00' HOUR TO MINUTE ;/* explicit session level setting to hawaii*/

Select tstamp AT LOCAL FROM test; -->12/31/2012 19:11:11.000000

select CAST(tstamp AS TIMESTAMP(6)) from test ; --> 12/31/2012 19:11:11.000000

We can see the result of casting and using AT LOCAL is exactly same.