CURRENT_TIMESTAMP
CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE for the session.
SQL> SELECT CURRENT_TIMESTAMP FROM dual;
CURRENT_TIMESTAMP
--------------------------------------------------------
08-JUL-12 10.17.33.899000 AM -07:00
SQL>
SQL> ALTER SESSION SET TIME_ZONE = 'EST';
Session altered.
SQL>
SQL> SELECT CURRENT_TIMESTAMP FROM dual;
CURRENT_TIMESTAMP
--------------------------------------------------------
08-JUL-12 01.17.34.040000 PM EST
SQL>
SQL> ALTER SESSION SET TIME_ZONE = 'PST';
Session altered.
SQL>
SQL>
SQL> SELECT CURRENT_TIMESTAMP FROM dual;
CURRENT_TIMESTAMP
--------------------------------------------------------
08-JUL-12 10.17.34.837000 AM PST
SQL>