Cómo funcionan las zonas horarias en MySQL

Variables de timezone definidas

Mysql establece el timezone o zonas horaria en distintas variables de sistema, las cuales sirven a diferentes propósitos.

Timezone del sistema

Se determina cuando el servidor inicia y se almacena en la variable global system_time_zone.

mysql> SELECT @@global.system_time_zone;

-> Hora estándar de Argentina

Timezone del servidor

Se almacena en la variable global time_zone e indica el timezone en el cual está operando el servidor. Por defecto, su valor es SYSTEM, lo que significa que es el mismo que el system_time_zone.

mysql> SELECT @@global.time_zone;

-> SYSTEM

Se puede cambiar su valor mediante

mysql> SET @@global.time_zone = 'timezone';

Timezone de la conexión

Se almacena en la variable de sesión time_zone e indica el timezone en el cual está operando la conexión. Por defecto, su valor es el de la variable global time_zone.

mysql> SELECT @@session.time_zone;

-> SYSTEM

Se puede cambiar su valor mediante

mysql> SET @@session.time_zone = 'timezone';

Valores de ‘timezone’ permitidos

Cuando debemos establecer un timezone podemos optar por los siguientes tipos de valor:

  • Offset de UTC
  • Nombre de zona horaria
  • Valor ‘SYSTEM’

Las zonas horarias nombradas disponibles en Mysql se pueden obtener mediante:

mysql> USE mysql;

mysql> SELECT name FROME time_zone_name;

Si no obtenemos ningún resultado, entonces deberemos cargar las zonas horarias en Mysql.

Uso de los timezones en Mysql

El campo de tipo TIMESTAMP convierte el valor desde el timezone de sesión a UTC para guardarlo y de UTC al timezone de sesión al devolverlo. También se utiliza el timezone de la sesión actual para el valor obtenido al invocar ciertas funciones, como por ejemplo, la función NOW().

Los campos de tipo DATE, TIME y DATETIME no se ven afectados por el timezone de la sesión como tampoco funciones asociadas a UTC, como puede ser UTC_TIMESTAMP().

Anuncios

Cargar zonas horarias en MySQL

Para ciertos consultas necesitamos utilizar zonas horarias en Mysql. Por defecto, las mismas no están cargadas. Para comprobarlo podemos ejecutar la siguiente consulta:

SELECT convert_tz('2014-07-01 3:14:15','UTC','America/Argentina/Buenos_Aires');

Si obtenemos NULL como resultado, debemos proceder de la siguiente manera según el sistema operativo:

Linux (Sistema con zoneinfo)

Ejecutar desde la línea de comandos:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

mysql_tzinfo_to_sql lee las zonas horarias del sistema y genera consultas SQL a partir de ellos. Mysql las recibe y carga las tablas de zonas horarias con los datos correspondientes.

 

Atención: Es posible que surja el siguiente error:

ERROR 1406 (22001) at line 38407: Data too long for column 'Abbreviation' at row 1

esto se debe a que el servidor está en modo estricto. Debemos cambiar el modo actual temporalmente, ejecutar el comando indicado y volver al modo original.

Windows (Sistema sin zoneinfo)

  1. Descargar de http://dev.mysql.com/downloads/timezones.html la última versión de las tablas de descripción de zonas horarias POSIX (En este momento POSIX standard Time zone description tables, version 2011n)
  2. Hacer un backup de la base de datos ‘mysql’
  3. Detener el servidor Mysql.
  4. Descomprimir el zip descargado y ubicar su contenido dentro del directorio de la base de datos ‘mysql’ (ubicado dentro del directorio de datos de Mysql), sobrescribiendo los archivos existentes.
  5. Reiniciar el servidor Mysql.

Comando mysql_tzinfo_to_sql a fondo

El comando mysql_tzinfo_to_sql (disponible desde Mysql 4.1.3) nos permite convertir la información de los timezones del sistema a consultas SQL, las que permiten popular el contenido de las tablas de timezones de Mysql.

En los sistemas operativos como Linux, Mac OS X y FreeBSD existe una base de datos de zonas horarias la cual es referenciada de varias maneras: tz database, tzdata, zoneinfo database o IANA Time Zone Database. De esta base de datos se nutre el comando en cuestión.

Para invocarlo basta con acceder a una línea de comandos e invocarlo en alguna de sus formas y enviar la salida a Mysql:

 

mysql_tzinfo_to_sql <TZ-DIR> | mysql -u root mysql

En este caso se indica el directorio donde se encuentra la base de datos zoneinfo.

Ejemplo:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

 

mysql_tzinfo_to_sql <TZ-FILE> <TZ-NAME> | mysql -u root mysql

En este caso indicamos el archivo y el nombre del timezone al cual se corresponde. Pero nos obliga a repetir la operación para cada zona horaria que necesitemos configurar.

 

mysql_tzinfo_to_sql --leap <TZ-FILE> | mysql -u root mysql

Por último, si necesitamos tener en consideración los “segundos adicionales” (leap seconds) debemos agregar el parámetro –leap junto al archivo del timezone.

 

Una vez ejecutado, lo más recomendable es reiniciar el servidor Mysql.

Terminología asociada a los horarios

Existen muchos términos referidos a la hora de hablar de fechas y horas. Les detallo las que me parecen más importantes:

Timezone, Huso horario o Zona horaria

Un timezone es una región de la Tierra que tiene una hora uniforme. Su definición se basó principalmente en fines comerciales, legales y sociales. La mayoría son offsets en cantidad de horas enteras con respecto al UTC.

GMT

GMT significa Tiempo Promedio de Greenwich (Greenwich Mean Time). Define un timezone de un hipotético día promedio en Greenwich. Durante mucho tiempo se utilizó GMT para definir los timezones con un offset respecto a él.

En algunos casos, se puede referir a un timezone que es equivalente a UTC (no tiene offset).

UTC o Zulu

UTC significa Tiempo Universal Coordinado (Coordinated Universal Time). Se define también como hora cero (Zero). Muchas veces se le dice “UTC-0” refiriéndose a la hora UTC, ya que tiene un offset de 0. En http://www.worldtimezone.com/ pueden ver los diferentes UTC y los países a los que afecta.

UTC estrictamente hablando no es un timezone, sino una escala de tiempo atómica que solo se aproxima a GMT.

En general, UTC se utiliza para el almacenamiento de las fechas y horas.

Offset

El offset es la diferencia en horas y minutos respecto a UTC. Por lo general se escribe en el siguiente formato

'±<HH>:<MM>'

aunque también se acepta:

'±<HH><MM>' o '±<HH>'

Entonces la zona horaria que para Argentina tiene un offset de -3 hs se podría escribir:

UTC-03:00

Localtime

Localtime es UTC más el offset correspondiente al timezone local. En general, se utiliza para la visualización de fechas y horas.

Epoch

Epoch (época en español) es un término asociado al inicio de un sistema particular.

Los sistemas que utilizan Epoch, representan una fecha y hora como el número de segundos que transcurren después de una fecha y hora específica.

En los sistemas Unix, se lo conoce como Unix epoch o Unix timestamp y su fecha de inicio es el 1 de Enero de 1970 a la medianoche GMT. En http://www.epochconverter.com/ encontrarán varias herramientas para realizar conversiones a partir de Unix timestamp.

Abreviaturas

Algunas zonas horarias se representan por abreviaturas como “EST, WST, CST” pero no son parte del estándar ISO 8601 y no se recomienda utilizarlas, ya que pueden ser ambiguas.

Daylight Saving Time (DST) u Horario de verano

El DST es el horario que sigue a la convención por la cual se adelantan los relojes para usar más la luz diurna. Normalmente los relojes se adelantan una hora a principios de la primavera y se regresan de nuevo en otoño. En http://www.timeanddate.com/time/dst/ pueden ver los próximos DST.

El problema introducido con los DST es que determinados horarios "no existen"
para cierto timezone. Por ejemplo, en Estados Unidos el 6 de abril de 2003
a las 2:00 se pasó al horario de verano. Los relojes pasaron de las 01:59:59
a las 03:00:00. Esto quiere decir que NO existieron los instantes desde
las 02:00:00 hasta las 02:59:59 del 6 de Abril allí.

Los sistemas que tienen que trabajar con fechas, horas y timezones deben tener en consideración estas situaciones.