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;
Database changed
mysql> SELECT COUNT(*) FROM time_zone_name;
568

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().

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.

Obtener el dump de una base de datos MySQL

Ya sea para mover el contenido de una base de datos de un lugar a otro como para generar un respaldo de la misma, es necesario obtener un dump que contenga tanto la estructura como los datos.

Para ello, desde la línea de comandos (recuerden que pueden agregar la ruta del ejecutable mysqldump a la variable Path en Windows):

mysqldump -u <USUARIO> -p [<OPCIONES>] <NOMBRE_DB> > dump.sql

a continuación se nos solicitará la clave de dicho usuario:

Enter password: ********

De esa manera obtendremos el conjunto de sentencias SQL que permitirán a futuro la restitución del contenido mediante la importación del dump a una base de datos Mysql

Opciones

Las opciones que se utilizan por defecto más importantes se detallan a continuación. Les recomiendo que cuando indiquen omitir una característica lo recomendable es agregar la opción al final de la lista para asegurarse que se aplica y no es revertida por otra opción que se indique después.

--comments, -i

Registrar información adicional como la versión del programa, el servidor y el host.

Omitir con --skip-comments.

--set-charset

Agrega la sentencia SET NAMES <default_charset>.

Omitir con --skip-set-charset.

--quote-names, -Q

Poner entre comillas los identificadores (nombres) entre backticks (`) o comillas dobles («).

Omitir con --skip-quote-names.

--tz-utc

Permite que los campos TIMESTAMP se guarden y recuperen entre servidores con diferentes zonas horarias, estableciendo el timezone de la conexión a UTC y agregando la sentencia SET TIME_ZONE=’+00:00′ al dump. De lo contrario, se usarían las zonas horarias del servidor origen y destino en cada operación, lo que modificaría los valores si fueran distintas.

Omitir con --skip-tz-utc.

--triggers

Incluye los triggers. Tener cuidado si se restituye en un servidor que no soporta múltiples triggers por evento, cuando se hayan dado el caso al generar el dump.

Omitir con --skip-triggers

--opt

Es la combinación de las siguientes opciones:

--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset

Omitir con --skip-opt.

--add-drop-table

Agrega la sentencia DROP TABLE antes de cada sentencia CREATE TABLE.

--add-locks

Encierra el dump de cada tabla entre sentencias LOCK TABLES y UNLOCK TABLES

+ La restitución es más rápida.

--create-options

Incluye las opciones de tabla específicas de Mysql en las sentencias CREATE TABLE. (No pude encontrar precisamente cuáles son)

--disable-keys, -K

Encierra las sentencias INSERT de cada taba con sentencias /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; y /*!40000 ALTER TABLE tbl_name ENABLE KEYS */;

+ Acelera la importación dado que los índices se crean luego de que todas las filas se insertan (solo para índices que no sean UNIQUE de tablas MyISAM).

--extended-insert, -e

Utiliza BULK INSERT (una sentencia con múltiples listas en VALUES) en lugar de una sentencia INSERT por cada registro.

+ El dump es más chico y las inserciones se hacen más rápido (considerar el valor de la variable max_allowed_packet)

--quick, -q

Fuerza a que obtengan de a una las filas de cada tabla en lugar de traelas todas juntas y procesarlas en memoria, antes de escribir el dump.

+ Útil para las bases de datos grandes

--lock-tables, -l

Bloquea todas las tablas de cada base de datos a la vez antes de hacer el dump. Utiliza un READ LOCAL para permitir inserciones concurrentes en tablas MyISAM. Para tablas transaccionales como InnoDB es mejor usar --single-transaction ya que no requiere bloquear las tablas.

Omitir con --skip-lock-tables.