There may be instances when you need to set your MySQL database to a different timezone from the one the server is in, but only for a specific website or application. It is possible to set the timezone on a per connection basis with MySQL and this post looks at how to do it. You can set the time zone for the server with the -timezone=timezonename option to mysqldsafe. You can also set it by setting the TZ environment variable before you start mysqld. The permissible values for -timezone or TZ are system dependent. Consult your operating system documentation to see what values are acceptable. Here we can see that the MySQL time zone is set as “System”. Which means current MySQL time zone depends on the system time. By default, MySQL time zone will be set to the server’s time zone. If you are interested in changing MySQL Time zone you can edit /etc/my.cnf configuration file. But it's necessary to uncomment the “date.timezone” setting and set the right timezone for you. Phpvalue maxexecutiontime 300 phpvalue memorylimit 128M phpvalue postmaxsize 16M phpvalue uploadmaxfilesize 2M phpvalue maxinputtime 300 phpvalue alwayspopulaterawpostdata -1 # phpvalue date.timezone Europe/Riga.
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:
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.
Mysql Set Server Time Zones
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 FilesMySQLMySQL Server 5.0datamysql
Set Mysql Server Timezone
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. Desktoptimezone-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 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:|