Check Your System
Before jumping in, it’s probably a good idea to check whether or not your system has already been configured for named time zones.
To do this, run the following query:
SELECT * FROM mysql.time_zone_name LIMIT 10;
Result:
Empty set (0.00 sec)
If your system has already been configured for named time zones, the above query will return a list of named time zones. In my case the query returned no results. This is because my system hasn’t yet been configured for named time zones.
Unix-Like Systems
The reason the above time zone tables are empty by default is because it’s usually better that the system handles the time zone, if possible.
Most Unix-like systems such as Linux, Mac OS X, FreeBSD, and Solaris have a zoneinfo database. This zoneinfo database can be loaded into the time zone tables in MySQL with the mysql_tzinfo_to_sql utility. The mysql_tzinfo_to_sql utility reads the system’s time zone files and creates SQL statements from them. MySQL then uses those statements to load the time zone tables.
If your system contains a zoneinfo database it’s preferable that you use this method to populate the time zone tables. Otherwise you may cause a difference in datetime handling between MySQL and other applications on your system.
To load the time zone tables, open a terminal window and run the following:
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
That’s it. The time zone tables should now be populated.
If your server requires password for user root try with -p flag:
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
Similar Posts:
- change / reset root password in mysql
- how to find size of the database via mysql console
- How to disable MySQL Strict Mode?
- MySQL error «Field xxx doesn’t have a default value»
- howto upgrade mariadb 10.x to 10.3.x on Centos 7