Identifies a field as a TIMESTAMP value with a displacement from UTC as defined for the system.
Syntax
TIMESTAMP [ ( 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.
- attributes
- Appropriate data type, column storage, or column constraint attributes.
ANSI Compliance
TIMESTAMP WITH TIME ZONE is ANSI SQL:2011 compliant.
Internal Representation of TIMESTAMP WITH TIME ZONE
Conceptually, TIMESTAMP WITH TIME ZONE is treated as a record composed of eight fields, defined appropriately for the Gregorian calendar.
| Field Name | Minimum Value | Maximum Value | Storage Format |
|---|---|---|---|
| SECOND | 00.000000 | 61.999999 This value accounts for leap seconds that can be added to the clock. |
INTEGER(DECIMAL(8,6)) |
| YEAR | 0001 | 9999 | SMALLINT |
| MONTH | 01 | 12 | BYTEINT |
| DAY | 01 | 28, 29, 30, or 31 (depending on the month and year) |
BYTEINT |
| HOUR | 00 | 23 | BYTEINT |
| MINUTE | 00 | 59 | BYTEINT |
| 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 12 bytes.
External Representation of TIMESTAMP WITH TIME ZONE
TIMESTAMP 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(25) | 'yyyy-mm-dd hh:mi:ss-hh:mi' or 'yyyy-mm-dd hh:mi:ss+hh:mi' |
|
n
where n is 1 - 6 |
CHAR(26+n) | 'yyyy-mm-dd hh:mi:ss.ss…-hh:mi' or 'yyyy-mm-dd hh:mi:ss.ss…+hh:mi' |
The following table shows examples of how TIMESTAMP WITH TIME ZONE types are exported in record and indicator modes.
| WHEN fractional_seconds_precision is … | THEN length is … | FOR example … |
|---|---|---|
| 6 | 32 | '2000-01-01 11:37:58.123456+08:00' |
| 0 | 25 | '2000-01-01 11:37:58-08:00' |
TIMESTAMP WITH TIME ZONE Format
For information about the TIMESTAMP WITH TIME ZONE format and how to change it, see TIME and TIMESTAMP Formats.
Implicit and Explicit TIMESTAMP WITH TIME ZONE Conversion
Teradata Database performs implicit conversion from CHARACTER to TIMESTAMP 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 TIMESTAMP WITH TIME ZONE.
Teradata Database also performs implicit conversion from TIMESTAMP WITH TIME ZONE to TIME and DATE types in some cases. However, implicit TIMESTAMP WITH TIMEZONE to TIME conversion is not supported for comparisons.
You can use CAST to explicitly convert CHARACTER to TIMESTAMP WITH TIME ZONE types and from TIMESTAMP WITH TIME ZONE to TIME or DATE types.
For more information, see “Data Type Conversions” in Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.