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

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 http://dev.mysql.com/downloads/timezones.html and download the ZIP file (the preferred version).

The version you download may be different.

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.

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.

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.

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.