16.10 - Examples: Using Time Formats - Teradata Database

Teradata Database SQL Data Types and Literals

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Programming Reference
featnum
B035-1143-161K

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 SQL Functions, Operators, Expressions, and Predicates.