15.00 - TimeDateWZControl - Teradata Database

Teradata Database Utilities

Product
Teradata Database
Release Number
15.00
Content Type
Configuration
Publication ID
B035-1102-015K
Language
English (United States)
Last Update
2018-09-25

TimeDateWZControl

Purpose  

Determines whether the built-in functions CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_DATE, DATE, and TIME reflect the session time and session time zone offset.

Field Group

General

Valid Settings

 

Setting

Description

0

Disabled. The values returned by the built-in time and date functions reflect the time value local to the Teradata Database server, but the time zone value that is defined for user or session. This is identical to a setting of 1.

1

Disabled. The values returned by the built-in time and date functions reflect the time value local to the Teradata Database server, but the time zone value that is defined for user or session. This is identical to a setting of 0.

2

Enabled. The values returned by the built-in time and date functions reflect the session time and session time zone. DateTime values are stored in the database as UTC.

3

Enabled. The values returned by the built-in time and date functions reflect the session time and session time zone. DateTime values without zone information are stored in the database as system local time.

System local time offset is defined either using an SDF file and the tdlocaledef utility, or using the System TimeZone Hour and System TimeZone Minute DBS Control settings.

Default

2   for:

  • new Teradata Database systems
  • systems that have been upgraded or re-initialized (sysinited) from prior Teradata Database releases in which TimeDateWZControl had been 0 and System TimeZone Hour or System TimeZone Minute had been non-zero.
  • Note: For systems that have been upgraded or sysinited from prior Teradata Database releases in which TimeDateWZControl had been a non-zero value, that value remains unchanged after the upgrade or sysinit.

    Changes Take Effect

    After the DBS Control Record has been written.

    Usage Notes  

    TimeDateWZControl must be enabled to use temporal tables. For more information on temporal tables, see Temporal Table Support.

    The session time zone can be set in the following ways:

  • TIME ZONE parameter of the CREATE USER statement
  • Sets the default time zone displacement from UTC used for sessions opened by the user.

  • SET TIME ZONE statement
  • Sets the time zone displacement from UTC for the current session. Overrides the time zone defined for the current user.

    If no session time zone is defined in either of these ways, the session time zone defaults to the Teradata Database system time zone, as defined by the DBS Control settings “System TimeZone Hour” and “System TimeZone Minute.”

    Examples  

    For the following examples, assume:

  • The time local to the Teradata Database server is 11:59:00 Coordinated Universal Time (UTC), January 31, 2010.
  • User TK lives in Tokyo, and has a time zone defined as +9 hours offset from UTC.
  • User LA lives in Los Angeles, and has a time zone defined as -8 hours offset from UTC.
  • User TK and User LA run the CURRENT_TIMESTAMP function at exactly the same time.
  • When this setting is enabled:

    For User TK, the CURRENT_TIMESTAMP function returns:

    2010-02-01 20:59:00.000000+09:00 

    For User LA, the CURRENT_TIMESTAMP function returns:

    2010-01-31 03:59:00.000000-08:00 

    When this setting is disabled:

    For User TK, the CURRENT_TIMESTAMP function returns:

    2010-01-31 11:59:00.000000+09:00

    For User LA, the CURRENT_TIMESTAMP function returns:

    2010-01-31 11:59:00.000000-08:00

    CURRENT_TIME and CURRENT_DATE functions display analogous behavior.

    Related Topics

     

    For more information on…

    See…

    Teradata Database built-in time and date functions

    SQL Functions, Operators, Expressions, and Predicates.

    Setting session time zones

    SET TIME ZONE, CREATE USER, MODIFY USER in SQL Data Definition Language.

    DateTime, Interval, and Period data types

    SQL Data Types and Literals.

    System TimeZone settings

    “System TimeZone Hour” on page 452 and
    “System TimeZone Minute” on page 454.

    Temporal tables

    Temporal Table Support.