Usage Notes - Advanced SQL Engine - Teradata Database

SQL Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
tpf1598412463935.ditamap
dita:ditavalPath
tpf1598412463935.ditaval
dita:id
B035-1143
lifecycle
previous
Product Category
Teradata Vantage™

Internal Representation of TIMESTAMP

Conceptually, TIMESTAMP is treated as a record composed of six 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

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

The length of the internal stored form is 10 bytes.

External Representation of TIMESTAMP

TIMESTAMP 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(19) 'yyyy-mm-dd hh:mi:ss'
n

where n is 1 - 6

CHAR(20+n) 'yyyy-mm-dd hh:mi:ss.ss…'

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

WHEN fractional_seconds_precision is … THEN length is … FOR example …
6 26 '1999-01-01 23:59:59.999999'
0 19 '1999-01-01 23:59:59'

TIMESTAMP Formats

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

Implicit and Explicit TIMESTAMP Conversion

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

Vantage also performs implicit conversions from TIMESTAMP to DATE and TIME types. However, implicit TIMESTAMP to TIME conversion is not supported for comparisons.

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

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