Oracle PL/SQL/Date Timezone/CURRENT TIMESTAMP

Compare the timestamp value between before and after "alter session set time_zone"

  
SQL>
SQL> create table t
  2  (c1 timestamp with time zone,
  3   c2 timestamp with local time zone)
  4  /
Table created.
SQL>
SQL> insert into t (c1,c2)values( current_timestamp, current_timestamp );
1 row created.
SQL>
SQL> select * from t;
C1
---------------------------------------------------------------------------
C2
---------------------------------------------------------------------------
16-JUN-08 05.01.27.439000 PM -08:00
16-JUN-08 05.01.27.439000 PM

1 row selected.
SQL>
SQL> alter session set time_zone = "-08:00";
Session altered.
SQL>
SQL> select * from t;
C1
---------------------------------------------------------------------------
C2
---------------------------------------------------------------------------
16-JUN-08 05.01.27.439000 PM -08:00
16-JUN-08 05.01.27.439000 PM

1 row selected.
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL> --

CURRENT_TIMESTAMP(): containing the current session time along with the session time zone

 
SQL>
SQL> --CURRENT_TIMESTAMP(): containing the current session time along with the session time zone.
SQL>
SQL> SELECT CURRENT_TIMESTAMP FROM dual;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
16-SEP-06 08.35.05.062000 PM EST
SQL>

Insert a row using the CURRENT_TIMESTAMP function

  
SQL>
SQL>
SQL> create table t
  2  (c1 timestamp with time zone,
  3   c2 timestamp with local time zone)
  4  /
Table created.
SQL>
SQL> insert into t (c1,c2)values( current_timestamp, current_timestamp );
1 row created.
SQL>
SQL> select * from t;
C1
---------------------------------------------------------------------------
C2
---------------------------------------------------------------------------
16-JUN-08 05.01.26.317000 PM -08:00
16-JUN-08 05.01.26.317000 PM

1 row selected.
SQL>
SQL> alter session set time_zone = "-08:00";
Session altered.
SQL>
SQL> select * from t;
C1
---------------------------------------------------------------------------
C2
---------------------------------------------------------------------------
16-JUN-08 05.01.26.317000 PM -08:00
16-JUN-08 05.01.26.317000 PM

1 row selected.
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL> --

select current_timestamp from dual to see the session time

  
SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
------------------------------------------------------
26-OCT-09 10.31.37.075000 AM -06:00
1 row selected.
SQL>