Examples: Using Time Formats - Teradata Vantage NewSQL Engine - 16.20

Teradata Vantageā„¢ Data Types and Literals

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1143-162K

Create a table using the FORMAT phrase to specify the output format:

CREATE TABLE t1
(f1 TIME(3) WITH TIME ZONE FORMAT 'HH:MI:SS.S(F)'
,f2 TIMESTAMP(4) FORMAT 'YYYY-MM-DDBHH:MI:SS.S(F)Z');

Populate the table using TIME and TIMESTAMP literals.

INSERT t1 (TIME '10:44:25.123-08:00',
TIMESTAMP '2000-09-20 10:44:25.1234');

Query the data:

SELECT f1, f2
FROM t1;

The query returns:

          f1                       f2
------------ ------------------------
10:44:25.123 2000-09-20 10:44:25.1234

Column f1 was defined as a TIME WITH TIME ZONE data type, but the FORMAT did not include the Z formatting character, so the time zone does not appear in the output. Column f2 was defined as a TIMESTAMP data type, and the FORMAT includes the Z formatting character. However the timestamp data is not associated with any time zone, so no time zone information appears in the output.

To display the time zone information for column f1, use the FORMAT phrase in the SELECT:

SELECT CAST (f1 AS TIME(3) WITH TIME ZONE FORMAT 'HH:MI:SS.S(F)Z')
FROM t1;

The query returns:

               f1
-----------------
10:44:25.123-8:00

For more information on TIME and TIMESTAMP literals, see Data Literals.

For more information on using the FORMAT phrase in data type conversions, see Teradata Vantageā„¢ SQL Functions, Expressions, and Predicates, B035-1145.