Monday, September 17, 2012

MySQL 5.1 - Default Time Zone

This is the easiest way to set the default timezone in MySQL, after looking through the manual (http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html). I happen to use phpMyAdmin, an easy-to-use web UI to administer MySQL, and currently wildly popular with cloud database providers e.g. OpenShift, Jelastic and XeRound.

At the 'Home' of phpMyAdmin (I'm using version 3.5.2.2), select 'More' > 'Variables' from the top menu bar. You will be intimidated by the massive list of MySQL variables. Just type 'time' to filter the list and you should be able to find the time_zone variable (http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_time_zone) as shown:

Click 'Edit' and enter the desired timezone offset from UTC e.g. for Malaysia (MYT) is +8:00. By default, this time_zone variable is set to SYSTEM which means MySQL will take the timezone settings from the OS.

Changes to the time_zone variable is effective immediately. To test, just run a simple query e.g. SELECT NOW() FROM SOMETABLE


Post a Comment