Oracle / PLSQL: TZ_OFFSET Function

This article is written about how to use the Oracle/PLSQL TZ_OFFSET characteristic with syntax and examples.

Description

The Oracle/PLSQL TZ_OFFSET characteristic returns the time sector offset of a value.

Syntax

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

TZ_OFFSET( timezone )

Parameters or Arguments

timezone A valid time zone name, a time sector offset from UTC, the SESSIONTIMEZONE function, or the DBTIMEZONE function. Some of the more normally 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 feature returns a time region offset value.

Note

To discover more time quarter values, you can run the following query:

SELECT DISTINCT tzname
FROM V$TIMEZONE_NAMES;

Applies To

The TZ_OFFSET feature can be used in the following variations of Oracle/PLSQL:

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

Example

Let’s appear at some Oracle TZ_OFFSET function examples and discover how to use the TZ_OFFSET function in Oracle/PLSQL.

For example:

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

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

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

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