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:
dateThe system should display the date, time, and time zone of the server. For example,
Tue Jan 21 11:33:35 MST 2020The 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.
Note: The sudo mysql -e "SET GLOBAL time_zone = 'timezone';" command modifies the global time zone in MySQL for the duration of the current service uptime. Once the MySQL service is restarted, the settings return to the default (SYSTEM).
For that reason, a much better option is setting the time zone by editing the MySQL configuration file.
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.cnfScroll 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).
Note: If you are working with a fresh MySQL install, your /etc/mysql/my.cnf file might not have any settings in it. If that is the case, scroll down to the bottom of the document and add the following:
[mysqld] default-time-zone = "+00:00"
Instead of +00:00 type the time zone you want to configure.
Restart the MySQL server to apply changes:
sudo service mysql restartCheck 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.
