Es bastante usual la necesidad de desarrollar una aplicación web para usuarios de muchas regiones con diferentes zonas horarias, algunas de éstas van a incluir también horarios de verano o invierno (DST), por lo cual tu aplicación debe estar preparada para ajustarse automáticamente a los cambios de hora de los países y así evitar errores costosos con la aritmética de las fechas. En este post te explicaremos cómo evitar y prevenir esos problemas.
Configurar y verificar las zonas horarias en MySQL
Debes asegurarte que MySQL sea configurado correctamente para trabajar con las zonas horarias, los detalles ya se encuentran explicados en el siguiente post: Mantener la hora correcta con MySQL automáticamente.
Muchas de las funciones temporales de MySQL son dependientes de la zona horaria de la sesión e influyen naturalmente en la hora que visualizamos; por eso casi siempre la configuramos con nuestra zona local, y por otro lado es recomendable que la zona horaria global esté conectada a la del sistema operativo del equipo donde se ejecuta el servidor MySQL. Podemos verificar que la sesión tiene configurada la hora adecuada así:
MariaDB [(none)]> SELECT @@GLOBAL.time_zone, @@SESSION.time_zone; +--------------------+---------------------+ | @@GLOBAL.time_zone | @@SESSION.time_zone | +--------------------+---------------------+ | SYSTEM | SYSTEM | +--------------------+---------------------+
Utilizar una zona horaria neutra
Una buena zona horaria neutra que pueda usarse como referencia para las distintas fechas que almacenemos es aquella que no tiene horarios de veranos o DST, ni tampoco cambios de hora. Entre ellas tenemos GMT y UTC siendo esta última la opción más recomendada.
Cuando necesitamos trabajar con varias zonas horarias, entonces debemos almacenar las fechas en la zona horaria UTC y recuperarlas en la zona horaria de la conexión. Al utilizar tipos TIMESTAMP para almacenar las fechas obtenemos todo esto de forma automática.
Establecer la zona de la sesión a UTC para hacer los cálculos
Al hacer esto, MySQL no tiene necesidad de hacer conversión alguna con los valores TIMESTAMP puesto que ya están almacenadas en la zona horaria UTC. Esto se logra de una manera muy fácil estableciendo el valor de la variable @@SESSION.time_zone con el comando SET, es decir:
SET @@SESSION.time_zone = 'UTC';
Calcular duraciones
Los cálculos más complejos están relacionados con los horarios de verano cuya aplicación se repite a cada año que pasa, generalmente los errores ocurren en los límites del DST. Tomaré como ejemplo la zona horaria de España 'Europe/Madrid': si guardamos una fecha para el día domingo 27 de octubre de 2019 a las 2:30 de la madrugada podría sorprendernos que al siguiente día amanezca con una hora diferente, ya que el tiempo transcurrido se hace cero por causa del cambio de horario.
Ese día cuando aún es horario de verano o CEST, se produce un cambio de horario a las 3:00 de la madrugada, los relojes deben colocarse a las 2:00 de la madrugada lo que quiere decir que se retrasan por una hora marcando así el comienzo del horario de invierno o CET.
Otra consecuencia es que ese día tendrá 25 horas, dado que el lapso de 2:00 a 3:00 de la madrugada ocurrirá dos veces, antes del cambio y después de éste. Son horas válidas las del día en cuestión en el intervalo de 2:00-3:00 antes del cambio y después del cambio, se diferencian por las siglas CEST y CET respectivamente.
Tomando esto en cuenta estudiemos el siguiente caso:
- Imagina que estás trabajando ese día y a las ‘2019-10-27 02:30:00’ CEST insertas un registro que posea un campo de fecha en una tabla de MySQL .
- Cuando se hagan las ‘2019-10-27 03:00:00’ CEST, el servidor hará el cambio de horario automáticamente y el tiempo se colocará a las ‘2019-10-27 02:00:00’ pero en horario CET.
- Sigues trabajando y a las ‘2019-10-27 02:30:00’ CET, insertas un segundo registro en la misma tabla.
Si ahora haces la consulta de la tabla, por un lado ves una misma fecha en horario CET para ambos casos ya que eso es lo que está mostrando MySQL. Para cualquier persona que desconozca que fueron introducidas en horarios distintos parecerá que no hay duración entre un evento y otro, cuando en realidad ha transcurrido una hora.
Si tampoco le indicas esto a MySQL, arrojará un cálculo incorrecto de cero cuando calcules la duración entre una fecha y otra con TIMEDIFF, por ejemplo:
SELECT TIMEDIFF(
(SELECT ts FROM t3 WHERE id = 2),
(SELECT ts FROM t3 WHERE id = 1)
);
La forma de corregir esto es indicarle a MySQL que tome en cuenta el cambio de horario para hacer el cálculo. Basta con que cambies la zona horaria en que estás a UTC, en el caso de ejemplo desde Europe/Madrid a UTC, para que aparezca la diferencia de 1 hora.
Aquí te dejo el script SQL con el ejemplo que almacena valores de fecha cerca de estos límites de tiempo en una tabla y luego los recupera para indicar el tiempo que ha transcurrido.
Para hacer la ejecución más realista vamos a modificar la fecha de MySQL arbitrariamente con el comando SET TIMESTAMP proporcionándole la fecha UNIX o la cantidad de segundos. Así podremos simular el avance del tiempo con una mera sumatoria de segundos para que puedas ver el momento exacto en que MySQL hace el cambio sin tener que esperar a que se cumpla el tiempo real.
Todas las instrucciones están comentadas y he puesto mensajes explícitos en la salida por consola para que sea más fácil de comprender.
/* Script dst.sql: calcula la duración entre dos fechas que se ven afectadas */
/* por un cambio de horario de verano a invierno en España */
/* Guarda el valor de la zona horaria */
SET @current_tz = @@SESSION.time_zone;
/* Crea la tabla t3 que contiene un campo para las fechas del tipo TIMESTAMP */
USE datetests;
CREATE TABLE t3(id INT, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP());
/* Establece la zona horaria de la sesión a España */
SET @@SESSION.time_zone = 'Europe/Madrid';
/* Establece la fecha y hora a media hora antes del cambio de horario */
/* Esto es a las 2:30 AM CEST en el horario de verano */
\! echo "";
\! echo "Adelanta el tiempo media hora antes del cambio a horario de invierno CET";
SET timestamp = (SELECT UNIX_TIMESTAMP('2019-10-27 02:30:00')); /* 1572136200 */
\! echo "Inserta esta primera fecha en la tabla";
INSERT INTO t3 (id) VALUES (1);
\! echo "Europe/Madrid 2019-10-27 2:30 CEST";
SELECT NOW();
\! echo "";
\! echo "Adelanto de media hora más para forzar el cambio de horario";
\! echo "Europe/Madrid 2019-10-27 3:00 CEST = Europe/Madrid 2019-10-27 2:00 CET";
SET @@SESSION.timestamp = @@SESSION.timestamp + 1800;
SELECT NOW();
\! echo "";
\! echo "Adelanta media hora más para que se hagan las 2:30, pero ya en horario de invierno CET";
SET @@SESSION.timestamp = @@SESSION.timestamp + 1800;
\! echo "Inserta la segunda fecha en la tabla";
INSERT INTO t3 (id) VALUES (2);
SELECT NOW();
/* Aquí se produce la magia */
SET @@SESSION.time_zone = 'UTC';
/* Lee los valores internos que están en UTC */
SET @first_date = (SELECT ts FROM t3 WHERE id = 1);
SET @second_date = (SELECT ts FROM t3 WHERE id = 2);
/* Visualiza la tabla en UTC */
\! echo "";
\! echo "Visualiza las fechas internas, cambiando la zona a UTC";
SELECT * FROM t3;
/* Calcula la diferencia como siempre */
\! echo "";
\! echo "Visualiza el tiempo transcurrido";
SELECT TIMEDIFF(@second_date, @first_date) AS elapsed_time;
/* Vuelve a establecer la zona */
SET @@SESSION.time_zone = 'Europe/Madrid';
\! echo "";
\! echo "Visualiza la tabla normalmente como se ven las horas locales, cambiando la zona a Europe/Madrid";
SELECT * FROM t3;
/* Restaura el valor de la zona horaria y la hora actual */
SET @@SESSION.time_zone = @current_tz;
SET @@SESSION.timestamp = DEFAULT;
Aquí te proporciono el enlace para que lo descargues: dst.sql
El script produce la siguiente salida cuando lo ejecutamos desde la consola:
mysql -uroot < ~/Downloads/dst.sql
Adelanta el tiempo media hora antes del cambio a horario de invierno CET Inserta esta primera fecha en la tabla Europe/Madrid 2019-10-27 2:30 CEST NOW() 2019-10-27 02:30:00 Adelanto de media hora más para forzar el cambio de horario Europe/Madrid 2019-10-27 3:00 CEST = Europe/Madrid 2019-10-27 2:00 CET NOW() 2019-10-27 02:00:00 Adelanta media hora más para que se hagan las 2:30, pero ya en horario de invierno CET Inserta la segunda fecha en la tabla NOW() 2019-10-27 02:30:00 Visualiza las fechas internas, cambiando la zona a UTC id ts 1 2019-10-27 00:30:00 2 2019-10-27 01:30:00 Visualiza el tiempo transcurrido elapsed_time 01:00:00 Visualiza la tabla normalmente como se ven las horas locales, cambiando la zona a Europe/Madrid id ts 1 2019-10-27 02:30:00 2 2019-10-27 02:30:00
El truco aquí es establecer la zona de la sesión a UTC de modo que MySQL aplique la aritmética con las fechas que almacena internamente. Esta misma solución aplica a los otros dos escenarios planteados al principio.
Conclusión
Para efectuar los cálculos de duraciones en escenarios complicados donde hay cambios de hora introducidos por horarios de verano, y/o por modificaciones de la hora legal, y/o por el uso de múltiples zonas horarias, es aconsejable que para los campos de fechas en MySQL uses el tipo de datos TIMESTAMP en el momento de la creación de tus tablas.
Debes mantener tanto la hora actual del sistema operativo como la de MySQL sincronizadas y actualizadas todo el tiempo, preferiblemente con respecto a la base de datos de zoneinfo que es bastante completa y confiable. Para ésto te recomiendo una vez más que sigas las instrucciones del post Cómo lograr y comprobar que MySQL mantenga la hora correcta automáticamente.
Espero que la temática planteada en este post contribuyan a mantener sincronizadas tus aplicaciones de ahora en adelante. Puedes seguir profundizando tus conocimiento de base de datos con la lectura del libro Aprende SQL de Styde.
Regístrate hoy en Styde y obtén acceso a todo nuestro contenido.
