Time Zones | Data Types and Literals | VantageCloud Lake - Time Zones - 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.

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

For information on defining session time zones, see SET TIME ZONE.

Storage and Manipulation of Values in UTC Form

By default, Vantage converts all TIME and TIMESTAMP values to UTC before 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'

The equal 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.

The TIME WITH TIME ZONE literal ' 20:00:00-08:00 ' is the lowest value in the sequence.

The counterargument to this explanation is that the time value '20:00:00-08:00' is '04:00:00' the next day. This is a correct assessment. However, the TIME data type has no concept of day adjustment. Therefore, consider using the TIMESTAMP data type for situations that may have such outcomes.

For a detailed example of this behavior, see ORDER BY Clause, 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, and is represented by +01:00.

Behavior of Time Values with UTC Offsets

Storing time values using UTC offsets is the following standard behavior.

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

To return this value without the time zone, use CAST to convert the value to TIMESTAMP (without time zone). In PST, the result is ' 1999-12-31 20:30 '. The identical query with the time zone offset for EST returns ' 1999-12-31 23:30 '.

Adjusting a time zone can change the displayed values for Year, Month, and Day fields.

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.

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 the previous (or next) day.

Related Information