In this tutorial, we will try to make the process of working with timezones in databases by showing you how to set a timezone in MySQL databases. Although this will not fully resolve how you work with time, it will help you understand various concepts about timezones in MySQL and other relational databases.”
Let’s explore.
MySQL Set Timezone – Method 1
You can use various methods to set and configure the timezone in a MySQL Server. The first one is setting the timezone in the session variable.
MySQL provides you with the @@session.time_zone variable, which stores the current timezone of the currently running session.
Each session that connects to the MySQL Server is assigned its session timezone setting. By default, this value is set to the global timezone value.
Therefore, if you want to modify the timezone of your current session without affecting other sessions connected to the server, this is the variable you change.
You can view the current timezone for your session by fetching the value stored by this variable. An example query is shown below:
Running the query above should return the output as shown:
In this case, we can see that the current session uses the timezone defined in the MySQL configuration file. You may need to set up timezone information tables in MySQL if you need to fetch the actual timezone names.
Be careful when performing modifications in system timezones, especially on Unix systems. You can learn more about timezone configuration in the resource below:
https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html
To set the timezone for your current session, you can use the SET command followed by the variable name and the target timezone.
For example, to set the current session timezone to Los Angeles, we can run the query as shown:
NOTE: Setting the timezone using the timezone name requires the system to include timezones. By default, Unix-like systems include zoneinfo files used for timezone information. On Windows, you may need to install the timezone information for your server manually.
You can also set the timezone using an offset value as shown:
Finally, you can confirm the current session timezone as:
Resulting output:
MySQL Set Timezone – Method 2
The second method you can use to set the timezone in your MySQL Server is by editing the global timezone variable.
Unlike the @@session.time_zone variable, which is limited to a specific session, the @@global.time_zone variable is adopted across all sessions.
You can check the global timezone value as shown:
By default, the value of the global timezone is set to the system as defined in the Server configuration file.
To modify the value of the global timezone, run the set command as:
The above statement sets the global timezone value to Los Angeles. Confirm with the select command:
Output:
You can also set the timezone as a UTC offset value.
MySQL Set Timezone – Method 3
The final method you can use to modify the server timezone is by editing the server configuration file.
Open the MySQL configuration file (my.cnf or my.ini) with your favorite text editor. In the [mysqld] section, locate the entry:
Change this value to your target timezone:
Save the file and restart the server.
This should load the new timezone.
Conclusion
In this post, we discussed the various methods and techniques to configure the timezone in the MySQL server at various levels.
Thanks for reading!!
