Oracle TZ_OFFSET

The Oracle TZ_OFFSET() function returns the time zone offset from UTC of a valid time zone name or the SESSIONTIMEZONE or DBTIMEZONE function name.

Syntax

The following illustrates the syntax of the Oracle TZ_OFSSET() function:

TZ_OFFSET(value)
Code language: SQL (Structured Query Language) (sql)

Arguments

1) value

The TZ_OFFSET() function accepts one argument which can be a valid time zone name e.g., ‘Europe/London’, a function name of SESSIONTIMEZONE or DBTIMEZONE, or a time zone offset from UTC (which simply returns itself).

Examples

A) Get time zone offset of a specific time zone

The following statement returns the time zone offset of the Europe/London time zone from UTC:

SELECT TZ_OFFSET( 'Europe/London' ) FROM DUAL;
Code language: SQL (Structured Query Language) (sql)

Here is the result:

+01:00
Code language: SQL (Structured Query Language) (sql)

B) Get time zone offset of the database time zone

The following example shows the time zone offset of the database time zone from UTC:

SELECT TZ_OFFSET( DBTIMEZONE ) FROM DUAL;
Code language: SQL (Structured Query Language) (sql)

The output is:

-07:00
Code language: SQL (Structured Query Language) (sql)

C) Get time zone offset of the session time zone

The following statement returns the time zone offset of the session time zone from UTC:

SELECT TZ_OFFSET( SESSIONTIMEZONE ) FROM DUAL;
Code language: SQL (Structured Query Language) (sql)

The output is:

-07:00
Code language: SQL (Structured Query Language) (sql)

Remarks

The following tables illustrates the valid time zones and their offsets from UTC:

Time zoneOffset
Africa/Freetown+00:00
UTC+00:00
America/Scoresbysund+00:00
Atlantic/Azores+00:00
Atlantic/Reykjavik+00:00
Atlantic/St_Helena+00:00
Etc/GMT+00:00
Etc/GMT+0+00:00
Etc/GMT0+00:00
Etc/GMT-0+00:00
Etc/Greenwich+00:00
GMT+00:00
GMT+0+00:00
GMT0+00:00
GMT-0+00:00
Greenwich+00:00
Iceland+00:00
Africa/Nouakchott+00:00
Africa/Algiers+01:00
WET+01:00
Africa/Tunis+01:00
Africa/Windhoek+01:00
Atlantic/Canary+01:00
Atlantic/Faeroe+01:00
Atlantic/Madeira+01:00
Eire+01:00
Etc/GMT-1+01:00
Europe/Belfast+01:00
Europe/Dublin+01:00
Europe/Lisbon+01:00
Europe/London+01:00
GB+01:00
GB-Eire+01:00
Portugal+01:00
Africa/Casablanca+01:00
Africa/Cairo+02:00
Poland+02:00
Africa/Johannesburg+02:00
Africa/Tripoli+02:00
Arctic/Longyearbyen+02:00
Asia/Gaza+02:00
CET+02:00
Egypt+02:00
Etc/GMT-2+02:00
Europe/Amsterdam+02:00
Europe/Belgrade+02:00
Europe/Berlin+02:00
Europe/Bratislava+02:00
Europe/Brussels+02:00
Europe/Budapest+02:00
Europe/Copenhagen+02:00
Europe/Gibraltar+02:00
Europe/Ljubljana+02:00
Europe/Luxembourg+02:00
Europe/Madrid+02:00
Europe/Monaco+02:00
Europe/Oslo+02:00
Europe/Paris+02:00
Europe/Prague+02:00
Europe/Rome+02:00
Europe/San_Marino+02:00
Europe/Sarajevo+02:00
Europe/Skopje+02:00
Europe/Stockholm+02:00
Europe/Tirane+02:00
Europe/Vatican+02:00
Europe/Vienna+02:00
Europe/Warsaw+02:00
Europe/Zagreb+02:00
Europe/Zurich+02:00
Libya+02:00
MET+02:00
Africa/Ceuta+02:00
Africa/Djibouti+03:00
Turkey+03:00
Africa/Mogadishu+03:00
Africa/Nairobi+03:00
Asia/Aden+03:00
Asia/Amman+03:00
Asia/Baghdad+03:00
Asia/Bahrain+03:00
Asia/Beirut+03:00
Asia/Damascus+03:00
Asia/Istanbul+03:00
Asia/Jerusalem+03:00
Asia/Kuwait+03:00
Asia/Nicosia+03:00
Asia/Qatar+03:00
Asia/Riyadh+03:00
Asia/Tel_Aviv+03:00
EET+03:00
Etc/GMT-3+03:00
Europe/Athens+03:00
Europe/Bucharest+03:00
Europe/Helsinki+03:00
Europe/Istanbul+03:00
Europe/Kaliningrad+03:00
Europe/Kiev+03:00
Europe/Minsk+03:00
Europe/Riga+03:00
Europe/Simferopol+03:00
Europe/Sofia+03:00
Europe/Tallinn+03:00
Europe/Vilnius+03:00
Indian/Mayotte+03:00
Israel+03:00
Africa/Khartoum+03:00
Asia/Dubai+04:00
Asia/Muscat+04:00
Asia/Tbilisi+04:00
Asia/Yerevan+04:00
W-SU+04:00
Europe/Moscow+04:00
Europe/Samara+04:00
Indian/Reunion+04:00
Etc/GMT-4+04:00
Asia/Kabul+04:30
Asia/Tehran+04:30
Iran+04:30
Asia/Karachi+05:00
Asia/Tashkent+05:00
Asia/Aqtau+05:00
Asia/Baku+05:00
Etc/GMT-5+05:00
Asia/Aqtobe+05:00
Asia/Calcutta+05:30
Asia/Yekaterinburg+06:00
Asia/Almaty+06:00
Etc/GMT-6+06:00
Indian/Chagos+06:00
Asia/Bishkek+06:00
Asia/Dacca+06:00
Asia/Dhaka+06:00
Indian/Cocos+06:30
Asia/Rangoon+06:30
Etc/GMT-7+07:00
Asia/Saigon+07:00
Asia/Omsk+07:00
Asia/Jakarta+07:00
Asia/Novosibirsk+07:00
Asia/Bangkok+07:00
Indian/Christmas+07:00
Asia/Manila+08:00
Asia/Macau+08:00
Asia/Macao+08:00
Asia/Kuching+08:00
Asia/Kuala_Lumpur+08:00
Asia/Krasnoyarsk+08:00
Asia/Kashgar+08:00
Asia/Hong_Kong+08:00
Asia/Harbin+08:00
Asia/Chungking+08:00
Asia/Chongqing+08:00
Singapore+08:00
ROC+08:00
PRC+08:00
Hongkong+08:00
Etc/GMT-8+08:00
Australia/West+08:00
Australia/Perth+08:00
Asia/Urumqi+08:00
Asia/Ujung_Pandang+08:00
Asia/Taipei+08:00
Asia/Singapore+08:00
Asia/Shanghai+08:00
Asia/Irkutsk+09:00
ROK+09:00
Asia/Seoul+09:00
Etc/GMT-9+09:00
Asia/Tokyo+09:00
Japan+09:00
Asia/Jayapura+09:00
Australia/Broken_Hill+09:30
Australia/Darwin+09:30
Australia/Adelaide+09:30
Australia/South+09:30
Australia/Yancowinna+09:30
Australia/North+09:30
Pacific/Guam+10:00
Pacific/Saipan+10:00
Asia/Yakutsk+10:00
Australia/ACT+10:00
Australia/Brisbane+10:00
Australia/Canberra+10:00
Australia/Hobart+10:00
Australia/Lindeman+10:00
Australia/Melbourne+10:00
Australia/NSW+10:00
Australia/Queensland+10:00
Australia/Sydney+10:00
Australia/Tasmania+10:00
Australia/Victoria+10:00
Etc/GMT-10+10:00
Australia/LHI+10:30
Australia/Lord_Howe+10:30
Etc/GMT-11+11:00
Pacific/Noumea+11:00
Asia/Vladivostok+11:00
Pacific/Norfolk+11:30
Pacific/Fiji+12:00
Pacific/Auckland+12:00
NZ+12:00
Kwajalein+12:00
Etc/GMT-12+12:00
Asia/Magadan+12:00
Asia/Kamchatka+12:00
Asia/Anadyr+12:00
Pacific/Wallis+12:00
Pacific/Wake+12:00
Pacific/Kwajalein+12:00
Pacific/Chatham+12:45
NZ-CHAT+12:45
Etc/GMT-13+13:00
Pacific/Tongatapu+13:00
Pacific/Fakaofo+14:00
Pacific/Kiritimati+14:00
Etc/GMT+1-01:00
America/Godthab-02:00
America/Noronha-02:00
Brazil/DeNoronha-02:00
America/Miquelon-02:00
Etc/GMT+2-02:00
America/St_Johns-02:30
Canada/Newfoundland-02:30
America/Sao_Paulo-03:00
America/Montevideo-03:00
Atlantic/Bermuda-03:00
Atlantic/Stanley-03:00
Brazil/East-03:00
Canada/Atlantic-03:00
Etc/GMT+3-03:00
America/Araguaina-03:00
America/Belem-03:00
America/Buenos_Aires-03:00
America/Cayenne-03:00
America/Fortaleza-03:00
America/Goose_Bay-03:00
America/Halifax-03:00
America/Maceio-03:00
America/Thule-03:00
US/Eastern-04:00
America/New_York-04:00
US/Michigan-04:00
America/Porto_Acre-04:00
America/Porto_Velho-04:00
America/Puerto_Rico-04:00
America/Rio_Branco-04:00
America/Santiago-04:00
America/St_Thomas-04:00
America/Thunder_Bay-04:00
America/Tortola-04:00
America/Virgin-04:00
Brazil/Acre-04:00
Brazil/West-04:00
Canada/Eastern-04:00
Chile/Continental-04:00
Cuba-04:00
EST5EDT-04:00
Etc/GMT+4-04:00
America/Anguilla-04:00
America/Aruba-04:00
America/Asuncion-04:00
America/Boa_Vista-04:00
US/East-Indiana-04:00
America/Cuiaba-04:00
America/Curacao-04:00
America/Detroit-04:00
America/Fort_Wayne-04:00
America/Grand_Turk-04:00
America/Guadeloupe-04:00
America/Havana-04:00
America/Indiana/Indianapolis-04:00
America/Indiana/Marengo-04:00
America/Indiana/Vevay-04:00
America/Indianapolis-04:00
America/Iqaluit-04:00
America/La_Paz-04:00
America/Louisville-04:00
America/Manaus-04:00
America/Martinique-04:00
America/Montreal-04:00
America/Montserrat-04:00
America/Caracas-04:30
Mexico/General-05:00
US/Central-05:00
US/Indiana-Starke-05:00
America/Panama-05:00
America/Rankin_Inlet-05:00
America/Winnipeg-05:00
Canada/Central-05:00
CST-05:00
CST6CDT-05:00
EST-05:00
Etc/GMT+5-05:00
Jamaica-05:00
America/Bogota-05:00
America/Cancun-05:00
America/Cayman-05:00
America/Chicago-05:00
America/Guayaquil-05:00
America/Indiana/Knox-05:00
America/Jamaica-05:00
America/Knox_IN-05:00
America/Lima-05:00
America/Mexico_City-05:00
MST7MDT-06:00
America/Managua-06:00
America/Inuvik-06:00
America/Guatemala-06:00
America/El_Salvador-06:00
America/Edmonton-06:00
America/Denver-06:00
America/Costa_Rica-06:00
America/Chihuahua-06:00
America/Cambridge_Bay-06:00
America/Boise-06:00
Mexico/BajaSur-06:00
Etc/GMT+6-06:00
Chile/EasterIsland-06:00
Canada/Saskatchewan-06:00
Canada/Mountain-06:00
Canada/East-Saskatchewan-06:00
America/Yellowknife-06:00
America/Tegucigalpa-06:00
America/Swift_Current-06:00
America/Shiprock-06:00
America/Regina-06:00
US/Mountain-06:00
Pacific/Easter-06:00
Navajo-06:00
America/Mazatlan-06:00
America/Ensenada-07:00
Canada/Pacific-07:00
US/Arizona-07:00
Canada/Yukon-07:00
America/Los_Angeles-07:00
America/Dawson_Creek-07:00
America/Whitehorse-07:00
America/Vancouver-07:00
America/Tijuana-07:00
Etc/GMT+7-07:00
America/Dawson-07:00
Mexico/BajaNorte-07:00
PST-07:00
America/Phoenix-07:00
MST-07:00
US/Pacific-New-07:00
US/Pacific-07:00
PST8PDT-07:00
US/Alaska-08:00
Etc/GMT+8-08:00
America/Anchorage-08:00
America/Nome-08:00
America/Juneau-08:00
Pacific/Pitcairn-08:00
Pacific/Gambier-09:00
America/Adak-09:00
America/Atka-09:00
Etc/GMT+9-09:00
US/Aleutian-09:00
Pacific/Marquesas-09:30
Pacific/Honolulu-10:00
Etc/GMT+10-10:00
Pacific/Johnston-10:00
US/Hawaii-10:00
Pacific/Tahiti-10:00
Pacific/Rarotonga-10:00
HST-10:00
Etc/GMT+11-11:00
US/Samoa-11:00
Pacific/Pago_Pago-11:00
Pacific/Niue-11:00
Pacific/Midway-11:00
Pacific/Samoa-11:00
Etc/GMT+12-12:00

In this tutorial, you have learned how to use the Oracle TZ_OFFSET() function to get the time zone offset from UTC of a time zone name.

Was this tutorial helpful?