Oracle PL/SQL - TIMESTAMP WITH TIME ZONE

Introduction

You can explicitly store the time zone for TIMESTAMP data types, as shown here:

declare
      variable1_ts TIMESTAMP[(precision)] WITH TIME ZONE;
      ...

Oracle can detect the time zone of both database server and the client computer.

You can see these values yourself by using the built-in functions DBTIMEZONE and SESSIONTIMEZONE.

The built-in function CURRENT_TIMESTAMP provides the timestamp in the session client time zone but not the database time zone.

Demo

SQL>
SQL> declare--   w ww .  ja v  a2  s  .c o m
  2       v_ts TIMESTAMP(6) WITH TIME ZONE
  3                          :=CURRENT_TIMESTAMP;
  4       v_tx VARCHAR2(2000);
  5  begin
  6       v_tx:=to_char(v_ts,'HH24:MI:SS.FF6 TZR ');
  7       DBMS_OUTPUT.put_line(v_tx);
  8       v_tx:=to_char(v_ts,'TZH TZM');
  9       DBMS_OUTPUT.put_line(v_tx);
 10  end;
 11  /
09:49:49.247000 -07:00
-07 00

PL/SQL procedure successfully completed.

SQL>

Related Topic