Skip to content

Instantly share code, notes, and snippets.

@brianburridge
Last active October 8, 2023 12:49
  • Star 31 You must be signed in to star a gist
  • Fork 7 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save brianburridge/11298396 to your computer and use it in GitHub Desktop.
How to load MySQL time zone tables from Mac OS time zone files

The easiest way to load the Mysql Time Zone tables from your Mac OS time zone fields is via this command:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

However, on many systems like mine that will fail because some of the time zone information is incompatible with the database schema for the time zone tables.

Therefore, you'll want to load the time zone information into a text file and edit it to only include the time zones you need. (Or, attempt to find the data breaking the import.)

mysql_tzinfo_to_sql /usr/share/zoneinfo > zone_import.sql

I edited the file to only include American time zones and UTC because they were all I need to test an app with.

After updating the zone_import.sql file, run this command to dump them to MySQL:

cat zone_import.sql | mysql -u root mysql

Once the file is imported, you'll need to restart MySQL. If you used HomeBrew to install it, these are your commands:

launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

Now that you have restarted MySQL, there is a simple test to check that the time zones are loaded. Launch the MySQL command line:

mysql -u root

Note: add -p if you need to enter a password to access your mysql db as root.

Once at the MySQL prompt, run this select:

select CONVERT_TZ(now(), 'UTC', 'America/New_York');

If either time zone does not exist in your time zone table, you will get back a 'NULL' value in the converted column. If both exist, you'll get back a timestamp and you're ready to go.

@flagoworld
Copy link

Or you can do what the dude at the bottom of the mysql docs page says... Worked perfectly for me.

mysql_tzinfo_to_sql /usr/share/zoneinfo | sed -e "s/Local time zone must be set--see zic manual page/local/" | mysql -u root mysql

Note by default you will probably have to /usr/local/mysql/bin/mysql_tzinfo_to_sql...etc

@ryantuck
Copy link

+1 @flagoworld that command worked for me on ubuntu 14.04.

@gorka
Copy link

gorka commented Sep 21, 2016

+1 @flagoworld worked like a charm on OSX

@lsteigerwald
Copy link

Does not work for me - has there something changed for the current version of OS X/MySQL?

@gsusmonzon
Copy link

Thanks!

@robjbrain
Copy link

I came here from a search for "load mysql timezones in dbngin" just to add that if you are using dbnin it's going to look something like this:

/Users/Shared/DBngin/mysql/8.0.27/bin/mysql_tzinfo_to_sql

(probably changing the version).

And I didn't have any issues with the timezones they got automatically skipped

Warning: Unable to load '/usr/share/zoneinfo/+VERSION' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment