Oracle TZ_OFFSET Function

SQL TZ_OFFSET FunctionOracle TZ_OFFSET Function is used to return the TIMEZONE offset of a value.


Oracle TZ_OFFSET Function Syntax

SELECT TZ_OFFSET(time_zone)
FROM table_name;

‘time_zone’ can be any time zone, a time zone offset from UTC, a sessiontimezone or a dbtimezone.

Examples of Timezones:-

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

Using the below SQL SELECT Statement you can view all the Timezones available.

SELECT DISTINCT tzname
FROM V$TIMEZONE_NAMES;

Oracle TZ_OFFSET – Using SQL SELECT Example

Oracle TZ_OFFSET Function is used with the SQL SELECT Statement.

For example, the Oracle TZ_OFFSET query below returns the Time Zone offset of “Asia/Tokyo” zone.

SELECT TZ_OFFSET('Asia/Tokyo')
FROM DUAL;

Will return “+09:00” as the offset for the ASIA/TOKYO time zone.


Oracle TZ_OFFSET – Using SESSIONTIMEZONE Example

Oracle TZ_OFFSET can be used with the SQL SESSIONTIMEZONE Function.

For example, the Oracle TZ_OFFSET query below returns the Timezone offset of the current session of the database.

SELECT TZ_OFFSET(SESSIONTIMEZONE)
FROM DUAL;

May return something like “+05:30” depending upon the session’s timezone offset.


Oracle TZ_OFFSET – Using DBTIMEZONE Example

Oracle TZ_OFFSET can be used with the SQL DBTIMEZONE to fetch the database time zone offset.

For example, the SQL TZ_OFFSET query below returns the timezone offset for the database.

SELECT TZ_OFFSET(DBTIMEZONE)
FROM DUAL;

May return something like “+00:00” depending upon the database timezone settings.


Leave a Reply

Your email address will not be published. Required fields are marked *