15.10 - Time Zones - Teradata Database

Teradata Database SQL Data Types and Literals

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1143-151K

All TIME and TIMESTAMP data is associated with time zones either explicitly or implicitly. A time zone is represented by a signed displacement from Universal Coordinated Time (UTC). All TIME and TIMESTAMP values are stored internally as UTC by default, but you can use the TimeDateWZControl DBS Control field to specify that DateTime values without time zone information be stored in the database as system local time. For details, see “DBS Control” in Utilities.

TIME and TIMESTAMP values submitted for storage can be literals that include time zone information or they can be explicitly specified with syntax that provides time zone displacement information with the submitted value.

 

WHEN time zone values are defined …

THEN …

implicitly

the default time zone displacement of the SQL session is assigned to them.

explicitly

the submitted time zone displacement is stored with the values.

For information on defining session time zones, see “SET TIME ZONE” in SQL Data Definition Language.

By default, Teradata Database converts all TIME and TIMESTAMP values to UTC prior to storing them. All operations, including hashing, collation, and comparisons that act on TIME and TIMESTAMP values are performed using their UTC forms.

For an example of hashing, consider the following TIMESTAMP literals:

   TIMESTAMP '1999-07-01 15:00:00-08:00'
   TIMESTAMP '1999-07-01 18:00:00-05:00'

Both values refer to the same time, which is expressed in UTC as follows:

   TIMESTAMP '1999-07-01 23:00:00'

Because they are equal to one another, both literals hash identically.

For an example of collation, consider the following TIME WITH TIME ZONE literals:

   TIME '08:00:00-08:00'
   TIME '12:00:00-08:00'
   TIME '15:00:00-08:00'
   TIME '20:00:00-08:00'

The correct collation of these values is as follows:

   TIME '20:00:00-08:00'
   TIME '08:00:00-08:00'
   TIME '12:00:00-08:00'
   TIME '15:00:00-08:00'

This nonintuitive outcome becomes more apparent when the values are converted to UTC.

 

Local Time Value

UTC Time Value

TIME '08:00:00-08:00'

TIME '16:00:00'

TIME '12:00:00-08:00'

TIME '20:00:00'

TIME '15:00:00-08:00'

TIME '23:00:00'

TIME '20:00:00-08:00'

TIME '04:00:00'

As you can see, the TIME WITH TIME ZONE literal ' 20:00:00-08:00 ' is actually the lowest value in the sequence.

The counterargument to this explanation is that the time value ' 20:00:00-08:00 ' is really ' 04:00:00 ' the next day. This is a correct assessment; however, the TIME data type has no concept of day adjustment. Because of this property, you should consider using the TIMESTAMP data type for situations that might have outcomes like the one presented by this example.

For a detailed example of this behavior, see “ORDER BY Clause” in SQL Data Manipulation Language, which contains an example where retrieving rows using an ORDER BY clause on a column with TIME data type may return unexpected results. The example also provides possible workarounds you can use to get the expected result.

When no explicit time zone is specified for TIME or TIMESTAMP data, the time zone for the current session is assigned to the data by default.

The current time zone for a session is defined relative to UTC.

For example, Eastern Standard Time (EST) is five hours earlier than UTC, so EST is indicated by the signed value -05:00. Eastern Daylight Time (EDT) is only four hours earlier than UTC, so EDT is indicated by the signed value -04:00. European time is ahead of UTC by one hour, so it is represented by +01:00.

 

WHEN a TIME or TIMESTAMP column is defined this way …

THEN information about its time zone is …

WITH TIME ZONE

stored explicitly using the fields TIMEZONE_HOUR and TIMEZONE_MINUTE to indicate the offset applicable to the data.

without WITH TIME ZONE

not stored with the data, so it is not possible to know what time zone was in effect at the time the data was stored.

Storing time values using UTC offsets results in the following standard behavior.

Suppose an installation is in the PST time zone and it is New Years Eve, 1998-12-31 20:30 local time.

The system TIMESTAMP WITH TIME ZONE for the indicated time is ' 1999-01-01 04:30-08:00 ' internally.

When you perform the CURRENT_TIMESTAMP function, it is in the form that includes TIME ZONE, and any external display converts the values into the appropriate values for the indicated time zone.

Should you want to return this value without the time zone, you could use CAST to convert the value to TIMESTAMP (without time zone). In PST, the result would be ' 1999-12-31 20:30 ', while the identical query performed with the time zone offset for EST returns the result ' 1999-12-31 23:30 '.

It is very important to realize that making a time zone adjustment can change the values for Year, Month, and Day fields when the result is displayed.

When timestamps are compared or used in an ORDER BY clause, a time zone adjustment does not change the comparison or ordering.

TIME adjustments to the time zone can also change what is displayed, just as TIMESTAMP adjustments do.

Note that the effect of adding or subtracting a time zone can change the comparison and ordering behavior because there are no higher order fields above the HOUR field to mark the crossover into what would be the previous (or next) day.

 

For more information on…

See…

setting session time zones

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

setting the system time zone

Database Administration.

using the AT LOCAL and AT TIME ZONE time zone specifiers to specify the time zone displacement in a DateTime expression

“ANSI DateTime Expressions” in SQL Functions, Operators, Expressions, and Predicates.

automatic adjustment of the system time to account for daylight saving time

“SDF file” and “Teradata Locale Definition Utility (tdlocaledef)” in Utilities.

  • defining whether DateTime values are stored in the database as UTC or as system local time
  • specifying whether or not the built-in functions CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_DATE, DATE, and TIME reflect the session time and session time zone
  • the TimeDateWZControl DBS Control field in Utilities.

    Locales that observe Daylight Savings Time (DST) set their clocks ahead one hour for a portion of the year for DST, then return their clocks to standard time by setting the clocks back. As a consequence, on the day when DST ends and clocks are turned back an hour, the times within this regained hour occur twice in the same day.

    Without a way to distinguish the DST (first) occurrence of a specific time value within this hour from the standard time (second) occurrence, these time values are ambiguous unless a time zone offset is explicitly specified with the time value.

    Teradata Database can automatically account for DST time switching through the use of time zone strings. Time zone strings allow you to define named time zones for locales based on their time offset from UTC. For locales that observe DST, time zone strings can include rules that define when DST starts and ends. Teradata Database uses this information to determine appropriate handling of time and date information, and to automatically adjust time zone offsets as appropriate for locales that observe DST.

    However, when time zone strings are used for these locales, but time zone offset values are not included with time values, Teradata Database cannot determine whether time values within this ambiguous hour should be interpreted and treated as DST or as standard time. In these cases, the DBS Control setting for DisplacementOnOverlap determines whether these times are assumed by Teradata Database to have a DST or standard time zone offset.

    Note: This issue occurs only when the following three conditions are true:

  • Time zone strings are used for DST locales
  • Time values do not include a time zone offset value
  • The DBS Control field TimeDateWZControl is set to 3 (date and time values without time zone offset information are stored using system local time)
  •  

    For more information on...

    See...

    Time zone strings

    Database Administration.

    SQL Functions, Operators, Expressions, and Predicates.

    the Teradata Locale Definition Utility chapter in Utilities.

    DisplacementOnOverlap
    TimeDateWZControl

    the DBS Control chapter in Utilities.