La función TZ_OFFSET en Oracle es una herramienta útil para trabajar con zonas horarias, ya que permite obtener el desplazamiento desde UTC (Tiempo Universal Coordinado) de una zona horaria válida. Esta función resulta especialmente valiosa en sistemas globales donde se gestionan usuarios o datos distribuidos en diferentes ubicaciones geográficas.
La función TZ_OFFSET acepta como argumento un nombre de zona horaria válido o valores dinámicos como SESSIONTIMEZONE o DBTIMEZONE, devolviendo una cadena con el desplazamiento en formato +/-HH:MI. Esto la hace ideal para validar configuraciones horarias, ajustar datos a zonas específicas o realizar cálculos basados en diferencias horarias.
En este artículo, primero mostraré la sintaxis básica de esta función para que puedas aplicarla correctamente en consultas SQL. Luego, incluiré ejemplos prácticos que ilustran cómo usarla para obtener el desplazamiento de zonas horarias específicas o ajustar datos en aplicaciones globales.
Para más detalles y ejemplos adicionales, puedes consultar la documentación oficial de ORACLE sobre TZ_OFFSET.
Tabla de Contenidos
ocultar
Sintaxis
Escribe la siguiente sintaxis para utilizar la función TZ_OFFSET en Oracle:
TZ_OFFSET(valor)
Ejemplo
SELECT TZ_OFFSET( 'Europe/London' ) FROM DUAL;
Zonas horarias disponibles
En la siguiente tabla puedes comprobar las diferentes zonas horarias disponibles y el valor que tienen:
| Zona Horaria | Valor |
|---|---|
| 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 |