TIMESTAMP WITH LOCAL TIME ZONE Type

TIMESTAMP WITH LOCAL TIME ZONE stores a timestamp in the database local time zone. The timestamp value is converted to the database time zone.


CREATE TABLE myTable(
id       INTEGER,
hiredate TIMESTAMP(4) WITH LOCAL TIME ZONE
); 

INSERT INTO myTable(id, hiredate) VALUES (1, TIMESTAMP '2012-07-18 09:19:30 EST'); 

SQL> SELECT * FROM myTable;

        ID                      HIREDATE
------------------------------------------------------------
         1    18-JUL-12 06.19.30.0000 AM


SQL>
SQL> ALTER SESSION SET TIME_ZONE = 'EST';

Session altered.

SQL>
SQL> SELECT * FROM myTable;

        ID                      HIREDATE
------------------------------------------------------------
         1    18-JUL-12 09.19.30.0000 AM


SQL>
SQL> ALTER SESSION SET TIME_ZONE = 'PST';

Session altered.

SQL>
SQL> SELECT * FROM myTable;

        ID                      HIREDATE
------------------------------------------------------------
         1    18-JUL-12 06.19.30.0000 AM


SQL>
SQL>

Home »
Oracle »
Data types »

Timestamp: