How To Change MySQL Server Time Zone

Sometimes you may need to change MySQL Server time zone to sync your application/data according to your customers & employees’ time zone. Here’s how to change MySQL server time zone.

How to Change MySQL Server Time Zone

There are 3 ways to change MySQL server time zone. We will look at each of them in detail.

1. Using Set GLOBAL command

Log into MySQL server and run the following command to set GLOBAL time_zone variable to GMT – 6:00 hrs

mysql> SET GLOBAL time_zone = '-6:00';

In the above command you need to mention the GMT offset for your time zone. In this case, our time zone is GMT – 6:00 hrs.

You can check the time zone with following command

mysql> SELECT @@global.time_zone;

You can also specify timezones instead of offsets. However, in this case, you will need to download and install MySQL time zones on your server.

Bonus Read : MySQL SHOW Indexes in Database

2. Using Set session command

Similarly, you can also set session-specific timezone values. Log into MySQL server and run the following command to set session-specific time_zone variable to GMT – 6:00 hrs

mysql> SET time_zone = '-6:00';

In the above command you need to mention the GMT offset for your time zone. In this case, our time zone is GMT – 6:00 hrs.

You can check the time zone with following command

mysql> SELECT @@session.time_zone;

Bonus Read : MySQL Compare Databases

3. Edit my.cnf file

You can also set MySQL server time zone in your server configuration file my.cnf. Open the file in a terminal.

$ sudo vi /etc/mysql/my.cnf

Scroll down to [mysqld] section. You will find the following line

default-time-zone = “+00:00”

Change the ‘+00:00’ to your time zone’s GMT offset, such as ‘-6:00’.

[mysqld]
...
default-time-zone='-06:00'

If your my.cnf file doesn’t have [mysqld] section, add it in the file

[mysqld]
default-time-zone='-06:00'

Restart MySQL Server to apply changes

$ sudo service mysql restart

Bonus Read : MySQL Alter Stored Procedure

4. Change MySQL Server Time Zone in cPanel

Here are the steps to set MySQL Server time zone in cPanel.

Log into cPanel WHM
Click Server Configuration section. You can search for it in on left pane if you cannot find it.
Click on Server Time option as shown below.
Select required time zone from drop-down list.
Click Change Timezone button. Based on selected option, the value under ‘Current Time’ will be updated. If you think the value shown is not correct, click ‘Sync time with Time Server’
If you are asked to reboot the server, do so from WHM itself.