17.10 - Usage Notes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1143-171K
Language
English (United States)

Internal Representation of TIME

Conceptually, TIME is treated as a record composed of three fields.

Field name Range of Values Storage Format
Minimum Maximum
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)

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

The length of the internal stored form is six bytes.

External Representation of TIME

TIME 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(8) 'hh:mi:ss'
n

where n is 1 - 6

CHAR(9+n) 'hh:mi:ss.ss...'

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

WHEN fractional_seconds_precision is … THEN the length is … FOR example …
6 15 '11:37:58.123456'
0 8 '11:37:58'

TIME Formats

For information about the TIME formats and how to change them, see TIME and TIMESTAMP Formats.

Implicit and Explicit TIME Conversion

Vantage performs implicit conversion from CHARACTER to TIME 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.

Vantage also performs implicit conversion from TIME to TIMESTAMP types in some cases. However, implicit TIME to TIMESTAMP conversion is not supported for comparisons.

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

For more information, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.