Howto Set Up Named Time Zones in MySQL

4
(1)

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:

289

How useful was this post?

Click on a star to rate it!

Average rating 4 / 5. Vote count: 1

No votes so far! Be the first to rate this post.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top