Custom Search

How to load time zone data for MySQL on Windows

MySQL provides developers with a rich set of Date and Time functions. One of the functions is CONVERT_TZ which converts a datetime value from one time zone to another. This function can use named time zones such as 'MET' or 'Europe/Moscow' from the time zone information tables in system database called mysql.

Named time zones can be used only if the time zone information tables in the mysql system database have been created and populated. The MySQL installation procedure creates the time zone tables in the mysql database, but does not load them with data. You need to load data manually for these time zone tables.

To view time zone tables, open SQLyog and view all tables in database mysql. There are five time zone tables but no data in them. These tables are part of the mysql system database:

  1. time_zone
  2. time_zone_leap_second
  3. time_zone_name
  4. time_zone_transition
  5. time_zone_transition_type

Tables in system database mysql

Load time zone data for Linux, FreeBSD, Sun Solaris, Mac OS X system

If your system has its own zoneinfo database (the set of files describing time zones), you should use the mysql_tzinfo_to_sql program for filling the time zone tables. Examples of such systems are Linux, FreeBSD, Sun Solaris, and Mac OS X. One likely location for these files is the /usr/share/zoneinfo directory. For detailed info about how to use mysql_tzinfo_to_sql program, refer to MySQL Manual.

Load time zone data for Windows or HP-UX system

Windows does not have a zoneinfo database, so you have to download the package of pre-built time zone tables available at the MySQL Developer Zone.

It's a rather simple process to load the pre-built time zone data into the mysql system database. Follow the six steps listed below to populate the time zone tables.

Step #1: Download the package that contains the data files of pre-built time zone tables.

Go to MySQL Developer Zone page here and download the ZIP file (the preferred version).

The version you download may be different.

Download the package of pre-built mysql time zone tables for Windows

Step #2: Unzip the downloaded ZIP file to a folder on your Desktop.

After unzip the package, you should see 15 database files - .frm, .MYD, and .MYI files for the MyISAM time zone tables. Three files for each time zone table.

MySQL database files for time zone tables

Step #3: Stop MySQL Server.

We need to stop MySQL server before we can load time zone data into the mysql system database.

To stop MySQL server, following these steps:

Start -> Settings -> Control Panel -> Administrative Tools -> Services -> MySQL

Right click on MySQL and choose Stop.

Stop MySQL database server

Step #4: Open mysql subdirectory of your MySQL server's data directory.

Open the data folder in MySQL installation directory. In the data folder, there is one directory for each database created in MySQL.

C:\Program Files\MySQL\MySQL Server 5.0\data\mysql

Inside this folder, you can see all the data files for the mysql system database.

mysql system database data file folder

Step #5: Overwrite time zone data files with the downloaded version.

Copy all 15 data files from your unzipped folder (e.g. \Desktop\timezone-2006p) and paste into data folder for the mysql system database. Overwrite all these .frm, .MYD, and .MYI files.

Step #6: Restart MySQL server.

After the data files have been overwritten, restart MySQL server to refresh these time zone tables.

Test if time zone tables have been populated properly

Open database mysql in SQLyog, view data in the time zone tables. You should see some data in there.

To check whether your time zone data is loaded properly for a change in Daylight Saving Time rules, use a test like the one recommended by MySQL manual. The example uses values that are appropriate for the 2007 DST (Daylight Saving Time) 1-hour change that occurs in the Eastern states of United States on March 11 at 2 a.m.

Open SQLyog and in Query window, run the following two queries:

The two queries below should return the same result
in the form of central US time 2007-03-11 01:00:00

Query 1:

Convert before Daylight Saving Time (DST)
value to central US time.
SELECT CONVERT_TZ('2007-03-11 2:00:00','US/Eastern','US/Central');

Query 2:

Convert after Daylight Saving Time (DST)
value to central US time.
SELECT CONVERT_TZ('2007-03-11 3:00:00','US/Eastern','US/Central');

The two queries convert US Eastern time to Central time when the 1-hour time change occurs in Eastern states. The use of named time zones requires that the time zone tables be used. The desired result is that both queries return the same result - the Eastern time is converted to the equivalent value in the US/Central time zone.

If you can see some data in the time zone tables and have also passed the DST test shown above, your time zone tables have been populated correctly.

Refer to MySQL Date and Time functions tutorials for detailed information about how to manipulate date and time data types in MySQL.

Stay current with Time Zone Changes

Loading the time zone information is not necessarily a one-time operation because the information changes occasionally. For example, the rules for Daylight Saving Time in the United States, Mexico, and parts of Canada changed in 2007. When such changes occur, applications that use the old rules become out of date and you may find it necessary to reload the time zone tables to keep the information used by your MySQL server current. See the notes at the end of this section.

Refer to MySQL documentation about how to update time zone tables in mysql database.


Other Recent Articles from the WAMP & LAMP category:

1.How to resolve Apache web server port 80 access problem
2.The free tools I use to build PHP and MySQL websites
3.Steps to install PHP 5.x on Windows as a development machine
4.How to install Apache 2.x web server on Windows
5.How to connect two different versions of MySQL server on the same computer
6.How to configure MySQL server 5.1 on Windows
7.How to install MySQL server 5.1 on Windows with screenshots
8.How to upgrade from PHP4 to PHP5
9.How to use Apache Virtual Host to run multiple local websites on Windows
10.Install all PEAR packages by yourself
11.How to install PEAR on Windows
12.How to use PHP and Microsoft SMTP Virtual Server to send emails
13.How to install PHP server-side scripting language on Windows
14.How to install Apache 1.3 web server on Windows
15.How to install two different versions of MySQL server on the same PC
16.How to configure MySQL server 4.1 on Windows
17.How to install MySQL server 4.1 on Windows with screenshots
18.How to set up DSN in ODBC Data Source Administrator on Windows

Copyright © 2017 All Rights Reserved.

This website is hosted by HostGator.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

Home | Feedback | Terms of Use | Privacy Policy