Do you think IIT Guwahati certified course can help you in your career?
No
Introduction
MySQL is an open source relational database management system. It is one of the most popular database management systems that help store and manipulate data efficiently.
When working with MySQL, it is important to Change TimeZone on MySQL Server to sync with the users of an application. Otherwise, it will lead to data inconsistencies and synchronization issues.
For example, if a customer enters a transaction at a particular time in their time zone, but the server records it in a different time zone, the data will be inconsistent.
Let's see how to change the time zone on a MySQL server step by step.
Check the Current Time Zone
Before making any changes, it is important to know the current time zone settings of the MySQL server.
To accomplish this, you can use the below command on the terminal after connecting the server over ssh
sudo mysql -e "SELECT @@global.time_zone;"
Output:
The above command selects the value of @@global.time_zone system variable and displays the current time zone setting of the MySQL server.
The time zone is represented as string, such as ‘UTC’ or ‘America/New_York’.
Display timestamp of MySQL server
Use the below command to Display the timestamp:
sudo mysql -e "SELECT NOW();"
Output:
The above command uses the NOW() function, which returns the current date and time based on the server's system clock.
The output will display the current time zone and date. The output format is typically in the standard ‘YYYY-MM-DD HH:MM:SS’ format.
Changing the Time Zone in MySQL
There are four ways how to change time zone on MySQL server. Let us look at each of them one by one in detail.
Using the SET GLOBAL time_zone
To set the global time zone, first login to the MySQL server:
We have two ways to select a time zone:
1. Using a named time zone (e.g., 'America/Chicago'):
SET GLOBAL time_zone = 'America/Chicago';
Output:
2. Using the UTC offset (e.g., -06:00):
SET GLOBAL time_zone = '-6:00';
Output:
Using the SET SESSION command
To change the time zone for the current session only, execute the following query:
SET time_zone = 'desired_time_zone';
This will override the global time zone setting for the duration of the session. Once the session is closed, the global time zone will apply again.
For Example:
SET time_zone = '-6:00';
Output:
Edit the MySQL Configuration File(my.cnf)
my.cnf is a necessary file that stores various settings and parameters of MySQL server.
In this case, we are setting the default_time_zone parameter to 'UTC' to establish a consistent time zone reference for the server and ensure that data operations are accurate and reliable.
The following command is used:
[mysqld]
default_time_zone = 'UTC'
Now, Restart the MySQL server to apply changes using the below command:
sudo service mysql restart
Change MySQL Server Time Zone in cPanel
Follow below simple steps on how to change time zone on MySQL server in cPanel:
Login to cPanel whm
Go to server configuration, then under it, select the Server time option or search the top right corner.
3. Set the desired time zone from the drop-down list
Frequently Asked Questions
What happens to the time stamps of existing data when changing the time zone?
The time stamps of existing data will not get affected by changing the time zone. However, the time stamps will display in the new time zone.
How to check the current time zone in MySQL?
To check the current time zone, you could run the query "SELECT @@global.time_zone, @@session.time_zone;" to view the global and session time zone settings.
Can the time zone be set using an offset like '-06:00'?
Yes, this offset is valid in MySQL. The format '+/-HH:MM' is what MySQL expects for named time zones or UTC offsets. If the offset and names abide by the rules, the time zone can be set like that.
How to change time zone on MySQL server and make it constant across server?
To make the time zone constant, add or change the "default_time_zone" value in the MySQL configuration file, which is my.cnf or my.ini. After making the changes restart the server.
Conclusion
Congratulations, you did a fantastic job!!. This article has gone through how to change time zone on MySQL server. We have seen three ways to change the time zone in detail. At last, some frequently asked questions have been discussed.