TIMESTAMP literal supports Time Zone (as offset from UTC). Default is SESSION Timezone: : Timestamp « SQL Data Types « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. SQL Data Types
3. Timestamp

SQL>
SQL> CREATE TABLE T (
  2  c1  DATE,
  3  c2  TIMESTAMP,
  4  c3  TIMESTAMP WITH TIME ZONE,
  5  c4  TIMESTAMP WITH LOCAL TIME ZONE);

Table created.

SQL>
SQL> DECLARE
  2
  3      v1  DATE;
  4      v2  TIMESTAMP;
  5      v3  TIMESTAMP WITH TIME ZONE;
  6      v4  TIMESTAMP WITH LOCAL TIME ZONE;
  7
  8  BEGIN
  9      v3 := TIMESTAMP '2002-11-03 03:00:00 -07:00';
 10      dbms_output.put_line(v3);
 11
 12      INSERT INTO t VALUES( v1, v2, v3, v4);
 13
 14  END;
 15  /

PL/SQL procedure successfully completed.

SQL>
SQL> select to_Char(c1, 'yyyy-mm-dd hh:mi:ss'), c2,c3,c4
  2  from t;

TO_CHAR(C1,'YYYY-MM
-------------------
C2
---------------------------------------------------------------------------
C3
---------------------------------------------------------------------------
C4
---------------------------------------------------------------------------


03-NOV-02 03.00.00.000000 AM -07:00



SQL>
SQL> DROP TABLE T;

Table dropped.

SQL>
SQL>