Oracle Date/Time Function - Oracle/PLSQL FROM_TZ Function






This Oracle tutorial explains how to use the Oracle/PLSQL FROM_TZ function.

FROM_TZ(x, time_zone) merges x and time_zone into one value.

It converts the TIMESTAMP x to the time zone specified by time_zone and returns a TIMESTAMP WITH TIMEZONE. The time_zone must be specified as a string of the form +|- HH:MI.

Syntax

The syntax for the Oracle/PLSQL FROM_TZ function is:

FROM_TZ( timestamp_value, time_zone_value )

timestamp_value is converted to a TIMESTAMP WITH TIME ZONE value.

time_zone_value is the TIME ZONE value.

Example


SQL> SELECT FROM_TZ(TIMESTAMP '2012-07-18 08:15:12.1234', '-8:00') FROM dual;

FROM_TZ(TIMESTAMP'2012-07-1808:15:12.1234','-8:00')
---------------------------------------------------------------------------
18-JUL-12 08.15.12.123400000 AM -08:00

SQL>




AT LOCAL

The AT LOCAL expression converts the source data into the local time equivalent:


SQL> SELECT FROM_TZ(
  2                  CAST(TO_DATE('2012-12-01 11:00:00','RRRR-MM-DD HH:MI:SS') AS TIMESTAMP), 'America/Los_Angeles'
  3                       ) AT LOCAL "East Coast Time"
  4  FROM DUAL;--   ww  w  .  j  a  v a2 s.  c  o  m

East Coast Time
---------------------------------------------------------------------------
01-DEC-12 11.00.00.000000 AM PST

SQL>