MySQL Time Zone Explained

April 11, 2024

Introduction

The MySQL server time zone affects how date and time data is stored, retrieved, and interpreted in the database. It ensures consistency and accuracy in time-related operations across different parts of an application or system.

Your system's time zone may differ from your server's MySQL time zone, making interpreting data in your database very difficult. Ideally, MySQL's time zone should be the same as yours to handle data more efficiently.

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

MySQL time zone explained

Prerequisites

Importance of Time Zones in MySQL

Time zones in MySQL are crucial for handling date and time data within databases and applications. Time zones affect the following:

  • Consistency. Time zone settings ensure consistent time and date data handling across different components of an application or system. Without proper time zone management, timestamp discrepancies lead to confusion and errors.
  • Correctness. Time zone information ensures that date and time values are stored, manipulated, and displayed accurately, accounting for differences in time offsets between geographic regions.
  • Localization. Time zone support allows apps to present date and time information in the user's local time, enhancing the user experience and content relevance.
  • Data Integrity. Proper time zone handling helps maintain data integrity by avoiding ambiguities and inconsistencies in date and time calculations. This is especially important in scenarios involving international users or distributed systems.

MySQL Time Zone Variables

The MySQL Server manages time zone settings through several variables. Those variables are:

  • time_zone - This variable defines the default time zone for the current server session. Its initial value is SYSTEM, which means that the server time zone is the same as the system time zone. The variable can be set globally at server startup or dynamically per session.
  • system_time_zone - The server system time zone. On server startup, it determines the host machine's time zone and uses it to set the system_time_zone system variable.
  • TZ - The TZ environment variable allows you to explicitly specify the system time zone for MySQL Server before you start mysqld. The values for TZ are system-dependent, so check your OS documentation to see what values are acceptable.

How to Check Current MySQL Time Zone Settings

Follow the steps below to check your current MySQL time zone settings and determine whether you need to make any changes:

1. Open a terminal window and, if you are working remotely, connect to your server over SSH using root.

2. Run the following command to check the current global time zone in MySQL:

sudo mysql -e "SELECT @@global.time_zone;"
Display the MySQL time zone in use.

By default, the system displays a value of SYSTEM for your time zone. This indicates that the MySQL time zone is synchronized with the server's time zone.

3. To display a timestamp from the server, use the date command:

date

The system should display the server's date, time, and time zone. For example:

Display the server timestamp in Linux.

The letter code indicates the time zone. In this case, it is set to Central European Summer Time (CEST).

4. To display a timestamp from the MySQL server, run the command below:

sudo mysql -e "SELECT NOW();"
Displaying a timestamp from the MySQL server.

The system displays the current time in MySQL.

How to Change MySQL Time Zone

This section outlines the steps for changing your MySQL time zone to be in sync with your system time. There are two ways to change the MySQL server time zone:

Choose your preferred method and follow the steps outlined below to change the time zone.

Option 1: Use the SET GLOBAL time_zone Command

The SET GLOBAL time_zone command allows you to set a new GMT value for the server's global MySQL time zone. Follow the steps below:

1. Open the terminal and use this syntax to set the new time zone:

sudo mysql -e "SET GLOBAL time_zone = '[value]';"

For example:

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.

2. Check the new value of your server's MySQL time zone setting:

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

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

Note: The SET GLOBAL time_zone command modifies the global time zone in MySQL for the current session. Once the MySQL service is restarted, the settings return to the default (SYSTEM). If you want the setting to persist between sessions, set the time zone by editing the MySQL configuration file.

Option 2: Edit the MySQL Configuration File

MySQL time zone settings can be changed by editing the main my.cnf configuration file. Follow the steps below:

1. Open the terminal and use a text editor to open the configuration file:

sudo nano /etc/mysql/my.cnf

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

Note: If you are working with a fresh MySQL installation, 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.

Save the file and exit.

In the example below, we set the MySQL Server time zone to +02:00 (GMT +2).

Changing the MySQL time zone through the MySQL configuration file.

3. Restart the MySQL server to apply changes:

sudo service mysql restart

4. Check the time zone and current time again:

sudo mysql -e "SELECT @@global.time_zone;"
sudo mysql -e "SELECT NOW();"
Checking MySQL time zone after editing the MySQL configuration file.

CONVERT_TZ Function in MySQL

The CONVERT_TZ() function in MySQL converts a datetime value from one time zone to another. It is particularly useful when working with international or multi-time zone data or when apps use real-time data updates or notifications. In such cases, the function enables the app to correctly display datetime values based on the users' time zones.

The syntax for the CONVERT_TZ() function is:

CONVERT_TZ (dt, from_tz,to_tz)
  • dt - The datetime value that you want to convert.
  • from_tz - The original time zone of the dt value.
  • to_tz - The time zone to which you want to convert the dt value.

For example, if you have a datetime value stored in a database in UTC, and you want to convert it to Eastern Standard Time (EST), the command is:

CONVERT_TZ(datetime_column, 'UTC', 'America/New_York')

MySQL Time Zone: Common Errors & Troubleshooting

This section lists some common issues with MySQL time zones and provides the solutions to mitigate the problems and ensure accurate datetime handling in your applications.

  • You can experience errors on the server if it is set to the SYSTEM timezone and does not support named timezones. To resolve the issue, populate the timezone tables on the server. 
  • If the SELECT NOW() statement returns values in UTC and not your local time, you must set the server to your current time zone. The same goes for UNIX_TIMESTAMP() returning the wrong value. Change the server time zone by following the steps outlined in the previous section. You can set the time zone for the server using the --timezone=timezone_name option for mysqld_safe, or by setting the TZ environment variable before you start mysqld.
  • Daylight Saving Time changes can affect datetime calculations, leading to unexpected results or inaccuracies. To resolve the issue, make sure to take DST changes into account and adjust datetime calculations accordingly. Use functions like CONVERT_TZ() to convert datetime values between time zones accurately.

Conclusion

This article has shown two different methods for changing the default time zone in MySQL. Ensuring your server time zone is correctly set is important to maintain accurate datetime conversions and consistency across applications and users accessing the database.

Next, learn how to set the time zone on a Ubuntu server or see how to get the current date and time in Python.

Was this article helpful?
YesNo
Bosko Marijan
Having worked as an educator and content writer, combined with his lifelong passion for all things high-tech, Bosko strives to simplify intricate concepts and make them user-friendly. That has led him to technical writing at PhoenixNAP, where he continues his mission of spreading knowledge.
Next you should read
How to Get Current Date & Time in JavaScript
December 6, 2023

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
March 12, 2024

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