Teradata Timestamp - Part 5 - Setting up Time Zone

How to set up a time zone?

Time zone can be set up at multiple places:

1. While setting up the user:

The TIME ZONE can be set while setting the user. This setting is at the user level.

CREATE USER sukul

   TIME ZONE = LOCAL  /* Use the system level setting */

                     = NULL /* No user level default. Either use the system level or the session level setting*/
                     ='08:00' /* explicit setting for time zone . This setting is at system level*/

                     =-':6:00'

2. Setting at system level

For system level setting we use the DBSCONTROL record in DB window.

MODIFY GENERAL 16=n /* Hour, n=12 to +13*/
MODIFY GENERAL 17=n /* Minutes, n=-59 to +59*/

3. Setting at session level

SET TIME ZONE LOCAL /* Use system level setting */

SET TIME ZONE USER /* Use user level setting */

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

Note that when setting the time  zone explicitly we make use of INTERVAL HOUR TO MINUTE.

Example:

select cast(current_timestamp as CHAR(35));

Result :  2013-04-13 05:47:23.470000+00:00  

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

select cast(current_timestamp as CHAR(35));

Result : 2013-04-13 05:47:24.870000+05:00  

SET TIME ZONE LOCAL; /* coming back to local time zone */