MySQL

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í:

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:

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.

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

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.