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.

tutorial on changing and how to set time zone in MySQL

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

command that displays the MySQL time zone in use

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.

Terminal output that indicates current time and date 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;"

using the global timezone command in MySQL

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.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).

example of how to set time zone in MySQL configuration file


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 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.


Next you should also read