TIME ZONES Teradata

In V2R3, Teradata has the ability to access and store both the hours and the minutes reflecting the difference between the user's time zone and the system time zone. From a World perspective, this difference is normally the number of hours between a specific location on Earth and the United Kingdom location that was historically called Greenwich Mean Time (GMT). Since the Greenwich observatory has been "decommissioned," the new reference to this same time zone is called Universal Time Coordinate (UTC).

A time zone relative to London (UTC) might be:

time zone relative to London (UTC)

A time zone relative to New York (EST) might be:

time zone relative to New York (EST)

Here, the time zones used are represented from the perspective of the system at EST. In the above, it appears to be backward. This is because the time zone is set using the number of hours that the system is from the user.

To show an example of TIME values, we randomly chose a time just after 10:00AM. Below, the various TIME with time zone values are designated as:

TIMESTAMP with time zone is represented as:

The default, for both TIME and TIMESTAMP, is to display six digits of decimal precision in the second's portion. Time zones are set either at the system level (DBS Control), the user level (when user is created or modified), or at the session level as an override.

Setting TIME ZONES

A Time Zone should be established for the system and every user in each different time zone. Setting the system default time zone:

Setting a User's time zone requires choosing either LOCAL, NULL, or a variety of explicit values:

Setting a Session's time zone:

A Teradata session can modify the time zone during normal operations without requiring a logoff and logon.

Using TIME ZONES

A user's time zone is now part of the information maintained by Teradata. The settings can be seen in the extended information available in the HELP SESSION request.

By creating a table and requesting the WITH TIME ZONE option for a TIME or TIMESTAMP data type, this additional offset is also stored.

The following SHOW command displays a table containing one timestamp column with TIME ZONE and one column as a timestamp column without TIME ZONE:

As rows were inserted into the table, the time zone of the user's session was automatically captured along with the data for TS_with_zone. Storing the time zone requires an additional 2 bytes of storage beyond the date+time requirements.

The next SELECT show the data rows currently in the table:

SELECT * FROM Tstamp_test ;
4 Rows Returnednext SELECT show the data rows currently in the table

Normalizing TIME ZONES

Teradata has the ability to incorporate the use of time zones into SQL for a relative view of the data based on one locality versus another.

This SELECT adjusts the data rows based on their TIME ZONE data in the table:


4 Rows ReturnedNormalizing TIME ZONES

Notice that the Time Zone value was added to or subtracted from the time portion of the time stamp to adjust them to a perspective of the same time zone. As a result, at that moment, it has normalized the different Times Zones in respect to the system time.

As an illustration, when the transaction occurred at 8:12 AM locally in the PST Time Zone, it was already 11:12 AM in EST, the location of the system. The times in the columns have been normalized in respect to the time zone of the system.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics