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.