Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Check the Current Time Zone
2.1.
Display timestamp of MySQL server
3.
Changing the Time Zone in MySQL
3.1.
Using the SET GLOBAL time_zone
3.1.1.
1. Using a named time zone (e.g., 'America/Chicago'):
3.1.2.
2. Using the UTC offset (e.g., -06:00):
3.2.
Using the SET SESSION command
3.3.
Edit the MySQL Configuration File(my.cnf)
3.4.
Change MySQL Server Time Zone in cPanel
4.
Frequently Asked Questions
4.1.
What happens to the time stamps of existing data when changing the time zone?
4.2.
How to check the current time zone in MySQL?
4.3.
Can the time zone be set using an offset like '-06:00'?
4.4.
How to change time zone on MySQL server and make it constant across server?
5.
Conclusion
Last Updated: Mar 27, 2024
Easy

How to Change Time Zone on MySQL Server?

Author Dhruv Rawat
0 upvote
Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

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.

How to Change Time Zone on MySQL Server


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:

output for select global time zone command

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:

output for select now()

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.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

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:

output for select global time zone command

2. Using the UTC offset (e.g., -06:00):

SET GLOBAL time_zone = '-6:00';


Output:

output for set global time zone command

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:

output showing the current session time zone

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:

  1. Login to cPanel whm
     
  2. Go to server configuration, then under it, select the Server time option or search the top right corner.
server config image


3. Set the desired time zone from the drop-down list

image showing configure timezone

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.

Here are some more related articles:

Check out The Interview Guide for Product Based Companies and some famous Interview Problems from Top Companies, like AmazonAdobeGoogle, etc., on CodeStudio.

Also, check out some of the Guided Paths on topics such as Data Structure and AlgorithmsCompetitive ProgrammingOperating SystemsComputer Networks, DBMSSystem Design, etc., as well as some Contests, Test SeriesInterview Bundles, and some Interview Experiences curated by top Industry Experts only on CodeStudio.

We hope you liked this article.

"Have fun coding!”

Live masterclass