Oracle Date/Time Function - Oracle/PLSQL TZ_OFFSET Function






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

The Oracle/PLSQL TZ_OFFSET function returns the time zone offset of a value.

TZ_OFFSET(time_zone) returns the offset for time_zone in hours and minutes. TZ_OFFSET() function gets the time zone offset hours.

Syntax

The syntax for the Oracle/PLSQL TZ_OFFSET function is:

TZ_OFFSET( timezone )

timezone can be a valid time zone name, a time zone offset from UTC, the SESSIONTIMEZONE function, or the DBTIMEZONE function.

Some of the more commonly used time zone names are:

Canada/Atlantic Pacific/Easter
Canada/Central Pacific/Honolulu
Canada/East-Saskatchewan Pacific/Kwajalein
Canada/Eastern Pacific/Pago_Pago
Canada/Mountain Pacific/Samoa
Canada/Newfoundland US/Alaska
Canada/Pacific US/Aleutian
Canada/Saskatchewan US/Arizona
Canada/Yukon US/Central
Europe/Dublin US/East-Indiana
Europe/Istanbul US/Eastern
Europe/Lisbon US/Hawaii
Europe/London US/Michigan
Europe/Moscow US/Mountain
Europe/Warsaw US/Pacific
Greenwich US/Pacific-New
Pacific/Auckland US/Samoa
Pacific/Chatham  

To find more time zone values, run the following query:

SELECT DISTINCT tzname
 FROM V$TIMEZONE_NAMES;




Example


SQL> SELECT TZ_OFFSET('PST')
  2  FROM dual;

TZ_OFFS
-------
-07:00

SQL>