15.10 - TIMESTAMP 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 TIMESTAMP value.

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 zero through six inclusive.

The default precision is six.

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.

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

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.

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'

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

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

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

The following example shows TIMESTAMP used in a query.

SELECT item, quantity, saletime
FROM sales
WHERE saletime > TIMESTAMP '2000-08-25 10:14:59'
AND saletime < TIMESTAMP '2000-08-25 10:30:01';

The difference between two TIMESTAMP types is an Interval type.

First define a table:

   CREATE TABLE BillDateTime
   (phone_no CHARACTER(10)
   ,start_time TIMESTAMP(0)
   ,end_time TIMESTAMP(0));

Now, determine the difference, specifying an Interval unit of DAY TO SECOND for the result:

   SELECT (end_time - start_time) DAY(4) TO SECOND
   FROM BillDateTime;

The DAY(4) specifies four digits of precision, and allows for a maximum of 9999 days, or approximately 27 years. The result looks like:

   5 16:49:20.340000

The following example compares two TIMESTAMP numbers to find out if they are within 30 minutes of each other.

First define a table:

   CREATE TABLE PhoneTime
   (phone_no CHARACTER(10)
   ,start_time TIMESTAMP(0)
   ,end_time TIMESTAMP(0));

Note that the difference between two TIMESTAMP types is an Interval type:

   SELECT phone_no
   FROM PhoneTime
   WHERE (end_time - start_time) DAY(4) TO MINUTE > INTERVAL '30' MINUTE;