[How To] Update or Change MySQL Server Time Zone

In this article will, we will go through the various options to change or update the MySQL server database timezone on the Linux, Unix servers. The MySQL timezone can be either updated globally or only for specific database. Hope this article will be useful to my fellow administrators getting request to change MySQL database server timezone in day to day activities.

The below steps should guide you to change / update the MySQL database server time zone:

Prerequisites

• Click here for the MySQL supported timezone.
• Click here for list of PHP supported timezone.
• Server shell access for SSH with root privileges.
• Check time_zone tables in MySQL database server. If it is empty then insert Zone Info data using following command: (optional)

 mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Systems like Linux, FreeBSD, Solaris, and Mac OS X stores zone information at /usr/share/zoneinfo directory. Else refer to https://dev.mysql.com/downloads/timezones.html for more details

To change / update MySQL database server timezone globally:

Execute below command with super admin privileges to set global timezone i.e. applicable for all databases present on the server

mysql>SET GLOBAL time_zone = timezone;

Example:

In the below example, the timezone will be set to America/Los_Angeles. Replace timezone value with the desired time zone you want to set.

mysql>SET GLOBAL time_zone = "America/Los_Angeles";

To change / update MySQL database timezone only for a user session / single database:

Select the database and execute below command

mysql>SET time_zone = timezone;

Example:

mysql>SET time_zone = Europe/London;

The timezone will be set to Europe/London in the above example.

To change / update MySQL database server timezone globally using my.cnf configuration file

• Open the my.cnf configuration in your favorite editor using admin privileges

vi /etc/my.cnf

• Insert the below line in the configuration file.

default_time_zone=timezone

Example:

default_time_zone=Asia/Calcutta

where timezone will be set to Asia/Calcutta.

Don't forget to restart MySQL server!

Please comment if you have any queries or suggestions, we will be happy to hear from you.