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:
A time zone relative to New York (EST) might be:
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 ;
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:
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.
Teradata Related Interview Questions
|Microstrategy Interview Questions||Informatica Interview Questions|
|MySQL Interview Questions||Oracle 11g Interview Questions|
|Hadoop Interview Questions||TeraData DBA Interview Questions|
|MYSQL DBA Interview Questions||Database Administration Interview Questions|
|DB2 SQL Programming Interview Questions||Hadoop Administration Interview Questions|
|Java Hadoop Developer Interview Questions||Informatica MDM Interview Questions|
|Informatica Admin Interview Questions||Hadoop Testing Interview Questions|
Teradata Related Practice Tests
|Microstrategy Practice Tests||Informatica Practice Tests|
|MySQL Practice Tests||Oracle 11g Practice Tests|
|Hadoop Practice Tests||TeraData DBA Practice Tests|
|MYSQL DBA Practice Tests||Database Administration Practice Tests|
|DB2 SQL Programming Practice Tests||Hadoop Administration Practice Tests|
Teradata Parallel Architecture
Fundamental Sql Using Select
On-line Help And Show Commands
Date And Time Processing
Character String Processing
Reporting Totals And Subtotals
Data Definition Language
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.