[MDBF-557] Faster timezone updates was: Remove DST support for "America/Mexico_City" timezone Created: 2023-04-10  Updated: 2023-04-11

Status: Open
Project: MariaDB Foundation Development
Component/s: None
Affects Version/s: N/A
Fix Version/s: None

Type: Task Priority: Minor
Reporter: Leonid Tsoi Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: timezones
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

MariaDB 10.4.26

Attachments: File zoneinfo-tzdata-2023c.sql    
Issue Links:
Relates
relates to MDBF-389 provide timezone data files Closed

 Description   

Mexico aborted DST for Mexico City and most of Mexico. There won't be DST changes in 2023.

CONVERT_TZ still has DST for "America/Mexico_City" timezone. 

MariaDB [mysql]> SELECT CONVERT_TZ('2023-01-01 00:00:00','UTC','America/Mexico_City');
 
+---------------------------------------------------------------+
 
| CONVERT_TZ('2023-01-01 00:00:00','UTC','America/Mexico_City') |
 
+---------------------------------------------------------------+
 
| 2022-12-31 18:00:00                                           |
 
+---------------------------------------------------------------+
 
1 row in set (0.012 sec)
 
 
 
MariaDB [mysql]> SELECT CONVERT_TZ('2023-05-01 00:00:00','UTC','America/Mexico_City');
 
+---------------------------------------------------------------+
 
| CONVERT_TZ('2023-05-01 00:00:00','UTC','America/Mexico_City') |
 
+---------------------------------------------------------------+
 
| 2023-04-30 19:00:00                                           |
 
+---------------------------------------------------------------+
 
1 row in set (0.000 sec)

Tested with latest time zone tables that were retrieved from here: https://mariadb.com/kb/en/time-zones/#mysql-time-zone-tables

Some related links: https://www.timeanddate.com/news/time/mexico-abolishes-dst-2022.html
https://www.timeanddate.com/news/time/mexico-time-zone-change-2022.html

 Comments   
Comment by Leonid Tsoi [ 2023-04-10 ]

Here are steps that were used to update time zone data.

We are using Oracle Linux 7. Found these bug reports where tzdata package was updated to cover changes with time zone in Mexico:

https://linux.oracle.com/errata/ELBA-2022-7404.html
https://linux.oracle.com/errata/ELBA-2022-8785.html

Here is RedHat site: https://access.redhat.com/articles/1187353

tzdata-2022g and tzdata-jave-2022g and newer releases should have updates. Currently 2023c is the latest version.

Once RPMs were upgraded, use a command below to update time zone data in MariaDB: 

mysql_tzinfo_to_sql /usr/share/zoneinfo|mysql -uroot -p mysql

Then restarted MariaDB service to start using new time zones.

Attached the output of the command below to this JIRA: 

mysql_tzinfo_to_sql /usr/share/zoneinfo > zoneinfo-tzdata-2023c.sql

After steps above, CONVERT_TZ stopped doing DST for Mexico_City time zone: 

MariaDB [(none)]> SELECT CONVERT_TZ('2023-05-01 00:00:00','UTC','America/Mexico_City');
 
+---------------------------------------------------------------+
 
| CONVERT_TZ('2023-05-01 00:00:00','UTC','America/Mexico_City') |
 
+---------------------------------------------------------------+
 
| 2023-04-30 18:00:00                                           |
 
+---------------------------------------------------------------+
 
1 row in set (0.002 sec)
 
 
 
MariaDB [(none)]> SELECT CONVERT_TZ('2023-01-01 00:00:00','UTC','America/Mexico_City');
 
+---------------------------------------------------------------+
 
| CONVERT_TZ('2023-01-01 00:00:00','UTC','America/Mexico_City') |
 
+---------------------------------------------------------------+
 
| 2022-12-31 18:00:00                                           |
 
+---------------------------------------------------------------+
 
1 row in set (0.000 sec)

Comment by Daniel Black [ 2023-04-11 ]

As I remember https://mirror.mariadb.org/zoneinfo/ is based on Ubuntu (mariadb base container) and is being worked through with https://bugs.launchpad.net/ubuntu/focal/+source/tzdata/+bug/2012599

Once it hits focal-updates (or jammy-update?) it should auto-update the sample data automaticity.

Comment by Daniel Black [ 2023-04-11 ]

I found the (non-public) script and it needs the ubuntu:focal image of Docker Official Images to be updated first at the moment (which triggers mariadb image, which our scripts pickup).

To do a more direct approach from IANA would involve skipping the distro packaging QA on it like:

$ curl --etag-compare zone_latest.txt  --etag-save zone_latest.txt https://data.iana.org/time-zones/tzdata-latest.tar.gz -o tzdata-latest.tar.gz

And then some compilation.

Generated at Thu Feb 08 03:38:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.