How to set the MySQL Timezone

MySQL timezone configuration is the important configuration. The MySQL database is a database that commonly using in the web technologies. This

How to set the MySQL Timezone

MySQL timezone configuration is the important configuration. The MySQL database is a database that commonly using in the web technologies. This database is mostly used with PHP programming language in the web world.

If need to give an example; the softwares like the WordPress, Opencart, Drupal are used with the MySQL Database. While doing the installing a server or if need to change the mysql timezone then you should refer this guide. Wrong MySQL Timezone configurations may be cause a runtime error. So that, if we use right timezone configurations, then we prevent timezone errors.

There are two ways to change the time zone configuration. One of them is updating the mysql config file. The next solution is to change the time zone using mysql query.

Change the MySQL Timezone by updating the “my.cnf” (Mysql Configuration) File

MySQL database has a configuration file named as “my.cnf”. There are many configurations about the MySQL in this file. We can define many things that affects the mysql database with this file. For example; we can define the maximum ram usage of the MySQL database here. To give another example, we can define the important configurations that affect the performance according to the server capacities. So, this file is the important file. We should be carefully while configuring. If we make a mistake in this file, this will be cause the heality problem of the mysql server. After making changes to this file, we should restart the MySQL service to apply the changes.

To change the MySQL Timezone configuration, open the my.cnf file and reconfigure the following line and add it to under the [mysqld]. The value is the GMT value. You can find your country GMT value on the internet.

For example;
default-time-zone='+03:00'

Change by running a mysql query

Firstly, to see your database current timezone, we can use the following query.

To Update the Global Timezone;

SELECT @@global.time_zone;

This query’s result will show your current timezone. To reconfigure it, you should run the following query after updating according to your need.

SET @@global.time_zone = '+03:00';

If you want to synchronize your database timezone with your system time, you should run the following query.

SET @@global.time_zone = 'SYSTEM';

To Update the Session Timezone;

If you want to update the mysql timezone of the current session, you can run the following code with above queries combinations.

SET @@session.time_zone = "+03:00";

As a result; How easy is MySQL time zone change! So our databases will work with the right configurations. These prevents the wrong date operations. We must always use databases with the correct configurations. If we encounter an issue, we must fix that in right methods.

Leave a Reply