Oracle Plsql Tz_offset Function

Oracle / PLSQL: TZ_OFFSET Function

This Oracle tutorial explains how to use the Oracle/PLSQL TZ_OFFSET function with syntax and examples.

Description

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

Syntax

The syntax for the TZ_OFFSET function in Oracle/PLSQL is:

TZ_OFFSET( timezone )

Parameters or Arguments

timezone

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

Returns

The TZ_OFFSET function returns a time zone offset value.

Note

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

SELECT DISTINCT tzname
FROM V$TIMEZONE_NAMES;

Applies To

The TZ_OFFSET function can be used in the following versions of Oracle/PLSQL:

  • Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

Example

Let's look at some Oracle TZ_OFFSET function examples and explore how to use the TZ_OFFSET function in Oracle/PLSQL.

For example:

TZ_OFFSET('US/Michigan')
Output: '-05:00'

TZ_OFFSET('-08:00')
Output: '-08:00'

TZ_OFFSET(sessiontimezone)
Output: '-07:00'  (depending on your configuration)

TZ_OFFSET(dbtimezone)
Output: '-07:00'  (depending on your configuration)