MySQL Time Zone Explained

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.

Prerequisites

• A user account with root privileges.
• A Linux server with MySQL installed.
• A root user account for the MySQL database.
• An SSH connection to your server (if working remotely).

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

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:

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

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:

• Using the SET GLOBAL time_zone command.
• By editing the MySQL configuration file.

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

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

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.

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

Save the file and exit.

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

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

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.