15.10 - TIME Data Type - Teradata Database

Teradata Database SQL Data Types and Literals

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1143-151K

Identifies a field as a TIME value.

This is not the value returned by the TIME function, which is formatted as a REAL number.

where:

 

Syntax element …

Specifies …

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 0 to 6 inclusive.

The default precision is 6.

attributes

appropriate data type, column storage, or column constraint attributes.

See “Core Data Type Attributes” on page 17 and “Storage and Constraint Attributes” on page 18 for specific information.

TIME is non-ANSI standard. The Teradata Database stores a TIME value in UTC.

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.

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'

For information about the TIME formats and how to change them, see “TIME and TIMESTAMP Formats” on page 289.

Teradata Database 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.

Teradata Database 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 “Data Type Conversions” in SQL Functions, Operators, Expressions, and Predicates.