2

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.

user756659
  • 3,372
  • 13
  • 55
  • 110

1 Answers1

1

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.

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
  • Running the command again will overwrite what is there though? With data already in those tables? – user756659 May 02 '20 at 19:26
  • 1
    You're asking if the command is idempotent. Yes, it should be. At least, there's nothing in the docs that say you have to clear them out first. Try running it twice and see what happens. :) – Matt Johnson-Pint May 02 '20 at 21:33
  • If you really want to experiment, first try it with an older tzdata installed. Then update it and run again. In between and after try some `tz_convert` statements, and observe the differences. You can see the tzdata release notes about what's new in each version. – Matt Johnson-Pint May 02 '20 at 21:35
  • I can confiurm the action is idempotent - run it as many times as you need – Sych May 29 '22 at 16:09