How to Set Timezone in MySQL

1. Display the Current Time in the Server in MySQL
2. Change Configuration File to Change the Time Zone in MySQL
3. Change Global time_zone Variable to Change the Time Zone in MySQL
4. Change Time Zone Only For Session in MySQL

There are a lot of articles written about MySQL and time zone management, but in this article, we will try to fill in the missing information. We will go through how to efficiently deal with the time zones.

Display the Current Time in the Server in MySQL

Sometimes, when working with a MySQL server, you need to deal with different time zones, which can sometimes confuse you. For example, you need to set MySQL Server time according to the customer’s time or migrate data to different servers and time zones.

When you switch to a Daylight Saving Time (DST) time zone, it can also happen. To ensure that the time zone is the same as planned, you must check or change the MySQL server time zone.

First of all, we need to check the current time in the MySQL server to check if it is the desired time zone.

SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2022-02-19 21:58:15 |
+---------------------+
1 row in set (0.00 sec)

We can also query the current global and session time zone values.

SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;

The default response is system, which means the server and system time zones are the same.

+--------------------+--------------------+
| @@GLOBAL.time_zone |@@SESSION.time_zone |
+--------------------+--------------------+
| SYSTEM             | SYSTEM             |
+--------------------+--------------------+
1 row in set (0.00 sec)

There are several ways to change the time zone; let’s see each one by one.

Change Configuration File to Change the Time Zone in MySQL

The easiest method is to edit the configuration file and set the default time zone. You can open the my.cnf configuration file from the terminal from the Linux server by the following code.

$ sudo vi /etc/mysql/my.cnf

After opening the file, we need to scroll down a bit to find the [mysqld] section, and here we will see default-time-zone = "+00:00". Here "+00:00" shows an offset from the GMT zone.

We can change it to Hungary’s time zone by setting "+01:00". In some cases, it can happen that you will not have the [mysqld] section; in that case, you need to add it to the end of the file.

After editing the file, we need to close it and restart the server using the following command.

$ sudo service mysql restart

Change Global time_zone Variable to Change the Time Zone in MySQL

We can also change the server’s global time zone and set a new GMT offset value.

SET GLOBAL time_zone = '-4:00';

In this command, you need to set the GMT offset of your time zone. We set it to GMT -4:00 hrs.

You can also use other definitions, like changing variables or writing specific time zone names.

SET GLOBAL time_zone = 'Europe/Budapest';
SET @@global.time_zone = '+02:00';

We can have a small issue if we use specific time zones’ names instead of numerical values. If we get an Unknown or incorrect time zone error, time zone data is not loaded in the MySQL server.

You can solve this issue by following Populating the Time Zone Tables.

Change Time Zone Only For Session in MySQL

As you see, we have queried two variables initially; one of them is system_time_zone.

By changing this variable, you will not change the global time zone but just the time zone per session; after restarting the server and running again, you will get back to the original time zone.

SET time_zone = "+00:00";