Actualice zonas horarias dentro de sus instancias RDS MySQL

Por Daniel Soto, Arquitecto de Soluciones en AWS

Tiempo de lectura

5 minutos

Nivel

300

Servicios usados

Amazon RDS MySQL, Aurora MySQL

Factores externos a una organización pueden causar cambios en cómo se manejan y actualizan las zonas horarias dentro de una aplicación. Cuando se trabaja con bases de datos relacionales la mejor práctica es manejar fechas y horas con UTC y luego convertir esto a la zona horaria adecuada en la capa de aplicación. Esto entrega flexibilidad para modificar las zonas horarias para algunos o todos los clientes. Sin embargo, aplicaciones que no siguen esta mejor práctica podrían verse [RMB1] afectadas, lo que puede tener un impacto negativo para nuestros clientes y genera la necesidad de actualizar zonas horarias dentro de un motor de bases de datos.

En este blog, mostramos cómo cargar y actualizar nuevas definiciones de zonas horarias en RDS MySQL y Aurora MySQL. Esto lo hacemos usando la base de datos de IANA para generar archivos SQL que podemos cargar en nuestra base de datos.

El procedimiento descrito en este blog fue realizado en un ambiente de AWS Cloud9, corriendo Amazon Linux 2, con acceso a una base de datos Aurora MySQL 5.7. Usamos de ejemplo el cambio de hora recientemente publicado en Chile, donde se atrasa una semana del 4 de septiembre al 11 de septiembre.

Generando los archivos de zona horaria

Primero, debemos descargar las definiciones más recientes desde la base de datos de zona horaria de IANA. Esto lo podemos hacer siguiendo las instrucciones en el repositorio de la base de datos. Sólo necesitamos la data para generar los archivos SQL.

mkdir tzdb
cd tzdb
wget https://www.iana.org/time-zones/repository/tzdata-latest.tar.gz
gzip -dc tzdata-latest.tar.gz | tar -xf -

Estos comandos descargan y descomprimen la base de datos en la carpeta llamada tzdb. Dentro de esta carpeta hay archivos de distintas regiones geográficas, como southamerica y northamerica. Usaremos estos archivos para generar los archivos de zona horaria, compilándolos con zic.

sudo zic southamerica

Este comando actualizará las definiciones de zona horaria del sistema operativo (ubicadas generalmente en /usr/share/zoneinfo) con los datos de la base de datos de IANA.

Generando el SQL de la base de datos

Dentro de la carpeta /usr/share/zoneinfo se encuentran todas las zonas horarias disponibles dentro del sistema. Usaremos una de estas para generar una nueva zona horaria mediante el comando de MySQL: mysql_tzinfo_to_sql.

Este comando recibe una carpeta con archivos de zona horaria como la carpeta ubicada en: /usr/share/zoneinfo, o un archivo de zona horaria y un nombre para asignarle a aquella zona horaria como el archivo ubicado en: /usr/share/zoneinfo/America/Santiago. En este caso tomaremos una única zona horaria por simplicidad.

Nota: Si mysql_tzinfo_to_sql no está instalado, se debe instalar mysql-community-server en la instancia.

sudo yum install https://dev.mysql.com/get/mysql80-community-release-el7-5.noarch.rpm

# Validar el repositorio:

ls /etc/yum.repos.d

sudo yum repolist

# Instala MySQL

sudo amazon-linux-extras install epel -y

sudo yum -y install mysql-community-server




mysql_tzinfo_to_sql /usr/share/zoneinfo/America/Santiago Santiago > santiago.sql

El archivo santiago.sql tiene todo el SQL necesario para modificar las tablas de sistema de MySQL con una nueva zona horaria, llamada “Santiago”. Es importante entender que este archivo crea una zona horaria nueva. Si se desea modificar una zona horaria ya existente, por ejemplo America/Santiago, es necesario agregar al comienzo de este archivo: DELETE FROM time_zone_name WHERE Name = ‘America/Santiago’;. Esto eliminará la zona horaria anterior y la reemplazará con la definición nueva.

Cargando el SQL

Finalmente cargamos este archivo en la base de datos desde una instancia que tenga acceso a ella. Esto lo podemos hacer directamente por la terminal con el siguiente comando:

mysql mysql -h <MYSQL_DB_ENDPOINT> -u <ADMIN_DB_USER> -p < santiago.sql

Si se está modificando una zona horaria ya existente, es necesario reiniciar la base de datos para limpiar el caché de la definición anterior.

Validando los cambios

Primero confirmamos que se insertó correctamente la zona horaria nueva “Santiago”.

MySQL [mysql]> SELECT * FROM time_zone_name WHERE name LIKE '%Santiago%'
+------------------------+--------------+
| Name                   | Time_zone_id |
+------------------------+--------------+
| America/Santiago       |          196 |
| posix/America/Santiago |          789 |
| right/America/Santiago |         1383 |
| Santiago               |         1785 |
+------------------------+--------------+
4 rows in set (0.00 sec)

Luego podemos validar que el cambio se realizó satisfactoriamente mediante la función: CONVERT_TZ. Su resultado debe reflejar que en la nueva zona horaria ya no se cambia el horario el 4 de septiembre.

MySQL [mysql]> SELECT CONVERT_TZ('2022-09-04 03:00:00','UTC','America/Santiago');                                                                          
+------------------------------------------------------------+
| CONVERT_TZ('2022-09-04 03:00:00','UTC','America/Santiago') |
+------------------------------------------------------------+
| 2022-09-03 23:00:00                                        |
+------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL [mysql]> SELECT CONVERT_TZ('2022-09-04 04:00:00','UTC','America/Santiago');
+------------------------------------------------------------+
| CONVERT_TZ('2022-09-04 04:00:00','UTC','America/Santiago') |
+------------------------------------------------------------+
| 2022-09-04 01:00:00                                        |
+------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL [mysql]> SELECT CONVERT_TZ('2022-09-04 03:00:00','UTC','Santiago');
+----------------------------------------------------+
| CONVERT_TZ('2022-09-04 03:00:00','UTC','Santiago') |
+----------------------------------------------------+
| 2022-09-03 23:00:00                                |
+----------------------------------------------------+
1 row in set (0.00 sec)

MySQL [mysql]> SELECT CONVERT_TZ('2022-09-04 04:00:00','UTC','Santiago');                                                                                   
+----------------------------------------------------+
| CONVERT_TZ('2022-09-04 04:00:00','UTC','Santiago') |
+----------------------------------------------------+
| 2022-09-04 00:00:00                                |
+----------------------------------------------------+
1 row in set (0.00 sec)

Y que el cambio de hora si se realizará en la nueva fecha del 11 de septiembre:

MySQL [mysql]> SELECT CONVERT_TZ('2022-09-11 03:00:00','UTC','Santiago');                                                                                   
+----------------------------------------------------+
| CONVERT_TZ('2022-09-11 03:00:00','UTC','Santiago') |
+----------------------------------------------------+
| 2022-09-10 23:00:00                                |
+----------------------------------------------------+
1 row in set (0.00 sec)

MySQL [mysql]> SELECT CONVERT_TZ('2022-09-11 04:00:00','UTC','Santiago');                                                                                   
+----------------------------------------------------+
| CONVERT_TZ('2022-09-11 04:00:00','UTC','Santiago') |
+----------------------------------------------------+
| 2022-09-11 01:00:00                                |
+----------------------------------------------------+
1 row in set (0.00 sec)

Conclusión

En este blog vimos cómo conseguir nuevas definiciones de zonas horarias, compilarlas e insertarlas en una base de datos MySQL. Esto sirve como una solución rápida para aplicaciones que no manejan sus fechas y horas en UTC, y deben modificar alguna zona horaria en base a legislaciones nuevas y cambios de definiciones previas. Siempre que sea posible, es recomendable manejar zonas horarias en la aplicación, y el dato persistido en UTC.

Acerca del autor

Daniel Soto es arquitecto de soluciones en AWS, que ayuda a sus clientes a diseñar arquitecturas que siguen las mejores prácticas del Well Architected Framework. Actualmente se enfoca en apoyar clientes a administrar sus bases de datos de manera efectiva.

Revisores

Rene Martínez es arquitecto de soluciones senior en AWS con 13 años de experiencia profesional en el rubro TI y más de 5 con tecnologías de nube específicamente. En su rol actual apoya a los clientes a encontrar las mejores arquitecturas y soluciones para sus necesidades, en especial, clientes del segmento Enterprise e industria financiera.

Angie Nobre Cocharero es especialista en bases de datos y arquitectura de soluciones en AWS para LATAM desde 2020. Tiene más de 20 años de experiencia implementando bases de datos y apoyando a múltiples clientes en la arquitectura de entornos de bases de datos, alta disponibilidad y recuperación ante desastres, también ha apoyado en migraciones y orientación para las mejores prácticas al implementar bases de datos.

Blog de Amazon Web Services (AWS)