Time Zones | Data Types and Literals | Teradata Vantage - Time Zones - Advanced SQL Engine - Teradata Database

SQL Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
zsn1556242031050.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1143
lifecycle
previous
Product Category
Teradata Vantage™

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 more information, see “DBS Control” in Teradata Vantage™ - Database Utilities , B035-1102 .

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 Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

Storage and Manipulation of Values in UTC Form

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 Teradata Vantage™ - SQL Data Manipulation Language, B035-1146, 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.

Implicit Time Zone Assignment

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.

Behavior of Time Values With UTC Offsets

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.

Related Topics

For more information on… See…
setting session time zones "SET TIME ZONE", "CREATE USER", "MODIFY USER" in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
setting the system time zone Teradata Vantage™ - Database Administration, B035-1093.
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 Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.
automatic adjustment of the system time to account for daylight saving time “SDF file” and “Teradata Locale Definition Utility (tdlocaledef)” in Teradata Vantage™ - Database Utilities , B035-1102 .
  • 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 Teradata Vantage™ - Database Utilities , B035-1102 .