TIME WITH TIME ZONE Data Type | Data Types and Literals | Teradata Vantage - TIME WITH TIME ZONE Data Type - 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™

Identifies a field as a TIME value with a displacement from UTC as defined for the system.

Syntax

TIME [ ( fractional_seconds_precision ) ]
  WITH TIME ZONE [ attributes [...] ]
fractional_seconds_precision
A single digit representing the number of significant digits in the fractional portion of the SECOND field.
Values for fractional_seconds_precision range from zero through six inclusive.
The default precision is six.
attributes
Appropriate data type, column storage, or column constraint attributes.
See Core Data Type Attributes and Storage and Constraint Attributes for specific information.

ANSI Compliance

TIME WITH TIME ZONE is ANSI SQL:2011 compliant.

Internal Representation of TIME WITH TIME ZONE

Conceptually, TIME WITH TIME ZONE is treated as a record composed of five numeric fields.

Field Name Minimum Value Maximum Value Storage Format
HOUR 00 23 BYTEINT
MINUTE 00 59 BYTEINT
SECOND 00.000000 61.999999

This value accounts for leap seconds that can be added to the clock.

DECIMAL(8,6)
TIMEZONE_HOUR -12.59 +14.00 BYTEINT
TIMEZONE_MINUTE BYTEINT

Although the record is composed of numeric fields, it is not treated as a numeric value.

The length of the internal stored form is eight bytes.

External Representation of TIME WITH TIME ZONE

TIME WITH TIME ZONE types are imported and exported in record and indicator modes as CHARACTER data using the ANSI format string and the site-defined client character set.

WHEN fractional_seconds_precision is … THEN the type is … AND the format is …
0 CHAR(14) 'hh:mi:ss-hh:mi'

or

'hh:mi:ss+hh:mi'

n

where n is 1 - 6

CHAR(15+n) 'hh:mi:ss.ss…-hh:mi'

or

'hh:mi:ss.ss…+hh:mi'

The following table shows examples of how TIME WITH TIME ZONE types are exported in record and indicator modes.

WHEN fractional_seconds_precision is … THEN length is … FOR example …
6 21 '11:37:58.123456+08:00'
0 14 '11:37:58-08:00'

TIME WITH TIME ZONE Format

For information about the TIME WITH TIME ZONE format and how to change it, see TIME and TIMESTAMP Formats.

Implicit and Explicit TIME WITH TIME ZONE Conversion

Teradata Database performs implicit conversion from CHARACTER to TIME WITH TIME ZONE types during assignment and comparison. This conversion is supported for CHAR and VARCHAR types only. You cannot convert a character data type of CLOB or CHAR/VARCHAR CHARACTER SET GRAPHIC to TIME WITH TIME ZONE.

Teradata Database also performs implicit conversion from TIME WITH TIME ZONE to TIMESTAMP types in some cases. However, implicit TIME WITH TIMEZONE to TIMESTAMP conversion is not supported for comparisons.

You can use CAST to explicitly convert CHARACTER to TIME WITH TIME ZONE types or from TIME WITH TIME ZONE to TIMESTAMP types.

For more information, see “Data Type Conversions” in Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.