Gestión y Husos Horarios en Oracle

Introducción
El manejo de fechas es una de las tareas más tediosas que existen en cualquier sistema, y por supuesto, Oracle no será una excepción. Vamos a ver cómo podemos utilizar las herramientas de TimeZone <desplazamiento horario> que nos proporcionan.

Por defecto -y a no ser que cambiemos esta de forma consciente- Oracle define el desplazamiento de la base de datos como GMT+0, es decir, no se encuentra desplazada. De esta forma, es capáz de obtener las diferentes representaciones de las fechas en función de su desplazamiento.

Vamos a ver cómo podemos saber si tenemos configurada nuestra base de datos y cuál es su <dbtimezone>

SQL> SELECT dbtimezone FROM DUAL;

DBTIME
------
+00:00

Ahora bien, nuestro TimeZone estará formado por Continente/Ciudad -en la mayoría de los casos- y en el nuestro será <Europe/Madrid>, pero podemos obtener una lista de todos los TimeZone que Oracle conoce haciendo la siguiente consulta

SQL> SELECT DISTINCT tzname FROM V$TIMEZONE_NAMES ORDER BY 1;

TZNAME
----------------------------------------------------------------
Africa/Algiers
Africa/Cairo
Africa/Casablanca
Africa/Ceuta
Africa/Djibouti
Africa/Freetown
Africa/Johannesburg
Africa/Khartoum
Africa/Mogadishu
Africa/Nairobi
Africa/Nouakchott
...
... continúa ...
...

Bien, una vez introducido los principales elementos en el uso de husos horarios, vamos a ver cómo operar con ellos. Lo primero que haremos será obtener la fecha-hora del sistema

SQL> SELECT CURRENT_TIMESTAMP FROM DUAL;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
01/02/10 14:28:02,990759 +01:00

Ahora, vamos a ver esta hora como es representada en Arizona, es decir, qué hora es allí. Para ello, vamos a utilizar una función <TZ_OFFSET> que nos dará el desplazamiento horario respecto a nuestra base de datos -recordar que es GMT+0-

SQL> SELECT TZ_OFFSET('US/Arizona') FROM DUAL;

TZ_OFFS
-------
-07:00

Muchos podéis pensar que resto TZ_OFFSET a la hora y ya tengo la hora desplazada, ..., ummm, pues la verdad es que no. Más adelante veremos en qué circustancias eso no nos sirve.

Así que, la solución es utilizar la función <FROM_TZ> que nos permite convertir una Hora a un desplazamiento en concreto.

Continuando con el ejemplo, queremos saber la hora en US/Arizona

SQL> SELECT
  2  FROM_TZ( CAST(CURRENT_TIMESTAMP AS TIMESTAMP), 'Europe/Madrid') 
  3  AT TIME ZONE 'US/Arizona'AS Hora_Arizona
  4  FROM DUAL;

HORA_ARIZONA
---------------------------------------------------------------------------
01/02/10 06:38:39,705886 US/ARIZONA

También podemos hacer las variantes que queramos, por ejemplo, representar una Fecha-Hora de US/Arizona en nuestro desplazamiento Europe/Madrid

SQL> SELECT
  2  FROM_TZ(CAST(TO_DATE('1980-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP),'US/Arizona')
  3  AT TIME ZONE 'Europe/Madrid' AS Hora_Madrid
  4  FROM DUAL;

HORA_MADRID
---------------------------------------------------------------------------
01/01/81 07:59:59,000000 EUROPE/MADRID

Un poco más complejo
Como todos los años, volveremos a tener dos cambios de hora: Horario de Verano y Horario de Invierno. Bien, es aquí donde tendremos los problemas y por qué no podemos restar el valor de TZ_OFFSET a nuestra hora -principalmente porque tenemos que comprobar en que día estamos-. El 28 de Marzo a las 02:00h serán las 03:00h -en España- y por lo tanto habrá un período de tiempo en el cual, el desplazamiento no se corresponderá con el TZ_OFFSET -hasta que no se hagan los ajustes en aquellos países que realizan el cambio- pero, además, si es un país donde no se hacen cambios tendremos un TZ_OFFSET diferente para verano y otro para invierno.

Todos estos cálculos los realiza Oracle por nosotros, así que, vamos a utilizarlos. Veamos el ejemplo del 28 de Marzo con un desplazamiento de Africa/Mogadishu

SQL> SELECT TZ_OFFSET('Africa/Mogadishu') FROM DUAL;

TZ_OFFS
-------
+03:00

SQL> SELECT
1    FROM_TZ(CAST(TO_DATE('2010-03-28 01:00:00','YYYY-MM-DD
2    HH24:MI:SS') AS TIMESTAMP),'Europe/Madrid')
3    AT TIME ZONE 'Africa/Mogadishu' AS Hora_Mogadishu
4    FROM DUAL;


HORA_MOGADISHU
---------------------------------------------------------------------------
28/03/10 03:00:00,000000 AFRICA/MOGADISHU

SQL> SELECT
1    FROM_TZ(CAST(TO_DATE('2010-03-28 03:00:00','YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP),'Europe/Madrid')
2    AT TIME ZONE 'Africa/Mogadishu' AS Hora_Mogadishu
3    FROM DUAL;

HORA_MOGADISHU
---------------------------------------------------------------------------
28/03/10 04:00:00,000000 AFRICA/MOGADISHU

Como vemos en el ejemplo, el desplazamiento para Africa/Mogadishu es GMT+3, por lo tanto, si España es GMT+1, cabe pensar que añadiendo 2 horas se puede obtener la hora. Sin embargo, si nos fijamos con más detalle en la segunda SQL -cuando la hora es las 03:00h- en Mogadishu son las 04:00h y no las 05:00h. Esto se debe a que en España se ha cambiado de horario y en Mogadishu no se realiza ese cambio.

Además, según este esquema la en España la franja horaria 02:01:00h a 02:59:59h no existe -ya que a las 02:00h son las 03:00h- y si intentamos hacer cualquier cambio horario en esta franja, Oracle mostrará el siguiente error:

SQL> SELECT
  2  FROM_TZ(CAST(TO_DATE('2010-03-28 02:10:00','YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP),'Europe/Madrid')
  3  AT TIME ZONE 'Africa/Mogadishu' AS Hora_Mogadishu
  4  FROM DUAL;
FROM_TZ(CAST(TO_DATE('2010-03-28 02:10:00','YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP),'Europe/Madrid')
        *
ERROR at line 2:
ORA-01878: specified field not found in datetime or interval

Conclusiones
Realmente el manejo de Fechas y Horas en diferentes husos y desplazamientos horarios puede darnos muchos problemas. Por ello, es de vital importancia utilizar las funciones que nos proporciona Oracle y sobre todo, tener el sistema con los últimos parches de TZ disponibles.

Referencias

• Oracle Software Patching
• Comando básicos de Solaris, y no tan básicos 
• Comandos básicos de Administración de Oracle sobre Solaris