Oracle PL/SQL Tutorial/Date Timestamp Functions/TO TIMESTAMP TZ

Examples of converting time zones to character strings

SQL>
SQL> DECLARE
  2     A TIMESTAMP WITH TIME ZONE;
  3     B TIMESTAMP WITH TIME ZONE;
  4     C TIMESTAMP WITH TIME ZONE;
  5  BEGIN
  6     A := TO_TIMESTAMP_TZ("2002-06-18 13:52:00.123456789 -5:00",
  7                          "YYYY-MM-DD HH24:MI:SS.FF TZH:TZM");
  8     B := TO_TIMESTAMP_TZ("2002-06-18 13:52:00.123456789 US/Eastern",
  9                          "YYYY-MM-DD HH24:MI:SS.FF TZR");
 10     C := TO_TIMESTAMP_TZ("2002-06-18 13:52:00.123456789 US/Eastern EDT",
 11                          "YYYY-MM-DD HH24:MI:SS.FF TZR TZD");
 12
 13     DBMS_OUTPUT.PUT_LINE(TO_CHAR(A,
 14        "YYYY-MM-DD HH:MI:SS.FF AM TZH:TZM TZR TZD"));
 15     DBMS_OUTPUT.PUT_LINE(TO_CHAR(B,
 16        "YYYY-MM-DD HH:MI:SS.FF AM TZH:TZM TZR TZD"));
 17     DBMS_OUTPUT.PUT_LINE(TO_CHAR(C,
 18        "YYYY-MM-DD HH:MI:SS.FF AM TZH:TZM TZR TZD"));
 19  END;
 20  /
PL/SQL procedure successfully completed.
SQL>
SQL>

SELECT TO_TIMESTAMP_TZ("2005-15:31.1234 -7:00", "YYYY-MM-DD HH24:MI:SS.FF TZH:TZM")

SQL> SELECT TO_TIMESTAMP_TZ("2005-05-13 07:15:31.1234 -7:00",
  2   "YYYY-MM-DD HH24:MI:SS.FF TZH:TZM")
  3  FROM dual;
TO_TIMESTAMP_TZ("2005-05-1307:15:31.1234-7:00","YYYY-MM-DDHH24:MI:SS.FFTZH:
---------------------------------------------------------------------------
13-MAY-05 07.15.31.123400000 AM -07:00
SQL>
SQL>

TO_TIMESTAMP_TZ("2002-52:00.123456789 -5:00","YYYY-MM-DD HH24:MI:SS.FF TZH:TZM")

SQL> DECLARE
  2     A TIMESTAMP WITH TIME ZONE;
  3     B TIMESTAMP WITH TIME ZONE;
  4     C TIMESTAMP WITH TIME ZONE;
  5  BEGIN
  6     A := TO_TIMESTAMP_TZ("2002-06-18 13:52:00.123456789 -5:00","YYYY-MM-DD HH24:MI:SS.FF TZH:TZM");
  7
  8     DBMS_OUTPUT.PUT_LINE(TO_CHAR(A,"YYYY-MM-DD HH:MI:SS.FF AM TZH:TZM TZR TZD"));
  9  END;
 10  /
PL/SQL procedure successfully completed.
SQL>
SQL>

TO_TIMESTAMP_TZ("2002-52:00.123456789 US/Eastern EDT","YYYY-MM-DD HH24:MI:SS.FF TZR TZD")

SQL> DECLARE
  2     A TIMESTAMP WITH TIME ZONE;
  3     B TIMESTAMP WITH TIME ZONE;
  4     C TIMESTAMP WITH TIME ZONE;
  5  BEGIN
  6     B := TO_TIMESTAMP_TZ("2002-06-18 13:52:00.123456789 US/Eastern","YYYY-MM-DD HH24:MI:SS.FF TZR");
  7     C := TO_TIMESTAMP_TZ("2002-06-18 13:52:00.123456789 US/Eastern EDT","YYYY-MM-DD HH24:MI:SS.FF TZR TZD");
  8
  9     DBMS_OUTPUT.PUT_LINE(TO_CHAR(B,
 10        "YYYY-MM-DD HH:MI:SS.FF AM TZH:TZM TZR TZD"));
 11  END;
 12  /
PL/SQL procedure successfully completed.
SQL>
SQL>

TO_TIMESTAMP_TZ(x,[format]) convert x to a TIMESTAMP WITH TIMEZONE with an optional format for x.

SQL>
SQL> SELECT TO_TIMESTAMP_TZ("2005-05-13 07:15:31.1234 PST",
  2   "YYYY-MM-DD HH24:MI:SS.FF TZR")
  3  FROM dual;
TO_TIMESTAMP_TZ("2005-05-1307:15:31.1234PST","YYYY-MM-DDHH24:MI:SS.FFTZR")
---------------------------------------------------------------------------
13-MAY-05 07.15.31.123400000 AM PST
SQL>