Как изменить часовой пояс mysql

How do I set the time zone of MySQL?

Your local time zone may differ from your server’s MySQL time zone. That makes interpreting data in your database very difficult. Ideally, MySQL time zone should be the same as your own to handle data more efficiently.

This guide will help you change the time zone on your MySQL server, making it easier and more intuitive to read server logs and other data.

Prerequisites

• A user account with root or sudo privileges
• A Linux server running MySQL
• A root user account for the MySQL database
• A terminal window / command line (Ctrl-Alt-T, Search > Terminal)
• An SSH connection to your server (if working remotely)

Find Current MySQL Time Zone

Open a terminal window. If you’re working remotely, connect to your server over SSH, using root.

Enter the following to check the current global time zone in MySQL:

sudo mysql –e “SELECT @@global.time_zone;”

By default, the system will display a value of SYSTEM for your time zone. This indicates that the time zone in MySQL is synchronized with the server’s time zone.

To display a timestamp from the server, enter the following:

date

The system should display the date, time, and time zone of the server. For example,

Tue Jan 21 11:33:35 MST 2020

The time zone is indicated by the letter code. In this case, it’s set to Mountain Standard Time (MST).

Display a timestamp from the MySQL server:

sudo mysql –e “SELECT NOW();”

The system should display the current time in MySQL.

Changing the Time Zone in MySQL

Option 1: Use the SET GLOBAL time_zone Command

Use this option to set a new GMT value for the server’s global MySQL time zone:

sudo mysql -e "SET GLOBAL time_zone = ‘-6:00’;"

Instead of -6:00, enter the GMT value you desire. If executed correctly, there is no response output.

Check the new value of your server’s MySQL time zone setting:

sudo mysql -e "SELECT @@global.time_zone;"

Once you change the time zone, previously stored datetime and timestamps are not updated.

Option 2: Edit the MySQL Configuration File

MySQL settings can be changed by editing the main my.cnf configuration file. Open the file for editing:

sudo nano /etc/mysql/my.cnf

Scroll down to the [mysqld] section, and find the default-time-zone = "+00:00" line. Change the +00:00 value to the GMT value for the time zone you want. Save the file and exit.

In the example below we set the MySQL Server time zone to +08:00 (GMT +8).

[mysqld]
default-time-zone = "+00:00"

Restart the MySQL server to apply changes:

sudo service mysql restart

Check the time zone and current time again:

sudo mysql –e “SELECT @@global.time_zone;”

sudo mysql –e “SELECT NOW();”

Conclusion

In this article, you have learned two different methods for changing the default time zone in MySQL.

If you also need to set the time zone on your Ubuntu server, refer to How to Set or Change Timezone on Ubuntu.

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";