How to Change MySQL Server Time Zone

February 19, 2020

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.

Was this article helpful?
YesNo
Dejan Tucakov
Dejan is the Head of Content at phoenixNAP with over 7 years of experience in Web publishing and technical writing. Prior to joining PNAP, he was Chief Editor of several websites striving to advocate for emerging technologies. He is dedicated to simplifying complex notions and providing meaningful insight into data center and cloud technology.
Next you should read
How to Get Current Date & Time in JavaScript
October 22, 2019

The Date object is useful for checking the date that visitors arrive at your website. This guide will walk...
Read more
How to Get the Current Date and Time in Python
August 14, 2019

The article shows you how to create a basic Python script that displays the current date and time. Find out...
Read more
How to Check the MySQL Version In Linux
July 11, 2019

It is essential to know which version of MySQL you have installed. The version number helps to determine if a...
Read more
How To Set or Change Timezone/Date/Time on Ubuntu 18.04
July 10, 2019

Most modern operating systems detect and synchronize the time with the NIST time server. NIST operates an...
Read more
  • © 2021 Copyright phoenixNAP | Global IT Services. All Rights Reserved.