Oracle PL/SQL/Date Timezone/LOCAL TIME ZONE

Change time zone

SQL> CREATE TABLE purchases_with_local_tz (
  2    product_id INTEGER,
  3    customer_id INTEGER,
  4    made_on TIMESTAMP(4) WITH LOCAL TIME ZONE
  5  );
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO purchases_with_local_tz (
  2    product_id, customer_id, made_on
  3  ) VALUES (
  4    1, 1, TIMESTAMP "2005-05-13 07:15:30 EST"
  5  );
1 row created.
SQL>
SQL>
SQL> ALTER SESSION SET TIME_ZONE = "EST";
Session altered.
SQL>
SQL> SELECT * FROM purchases_with_local_tz;
PRODUCT_ID CUSTOMER_ID MADE_ON
---------- ----------- ---------------------------------------------------------------------------
         1           1 13-MAY-05 07.15.30.0000 AM
SQL>
SQL> drop table purchases_with_local_tz;
Table dropped.
SQL>

TIMESTAMP(4) WITH LOCAL TIME ZONE

SQL> CREATE TABLE purchases_with_local_tz (
  2    product_id INTEGER,
  3    customer_id INTEGER,
  4    made_on TIMESTAMP(4) WITH LOCAL TIME ZONE
  5  );
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO purchases_with_local_tz (product_id, customer_id, made_on)
  2                               VALUES (1, 1, TIMESTAMP "2005-05-13 07:15:30 EST");
1 row created.
SQL>
SQL> SELECT * FROM purchases_with_local_tz;
PRODUCT_ID CUSTOMER_ID MADE_ON
---------- ----------- ---------------------------------------------------------------------------
         1           1 13-MAY-05 04.15.30.0000 AM
SQL>
SQL> drop table purchases_with_local_tz;
Table dropped.
SQL>
SQL>