Search code examples
mysqltimezonezoneinfo

mysql_tzinfo_to_sql - updating the timezone tables after you already loaded them and other questions

I just used mysql_tzinfo_to_sql to load the zoneinfo database into mysql. They all imported fine.

[root@db ~]# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql mysql

I have some questions that I can't seem to find answers for :

  • Are the names in time_zone_name the same as those used in PHP? I am assuming PHP uses the same /usr/share/zoneinfo information so the names would be the same as well right?
  • It mentions the tables need to be repopulated every now and then when timezone data changes. Is this when tzdata is updated though yum updates? Is tzdata my /usr/share/zoneinfo information?
  • If the above is true, tzdata updates, and I need to repopulate my tables... how do I do that? Can I just run [root@db ~]# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql mysql again? Will that command overwrite the information that is currently there or do I need to manually delete all the table entries before doing so?

Thanks for any information on the above. So far the timezone names used in PHP seem to match up, but I have not looked into it further other than a few tests. As for the last two... I am just trying to get ahead of things so if/when the tables need updated I do not have trouble.

Solution

  • MySQL and your OS both use time zone from the IANA time zone database.

    On many Linux distributions, these come from the tzdata package distribution, which installs them into /usr/share/zoneinfo.

    So, yes - they are all the same.

    With regard The MySQL time zone tables, the documentation says:

    If your system has its own zoneinfo database, reload the MySQL time zone tables whenever the zoneinfo database is updated.

    So, yes - you should simply run the command again any time you update to the latest tzdata package.