How to view and modify the time zone in MySQL

Today I found that a program inserted an incorrect time, and the field was configured with the default value CURRENT_TIMESTAMP. I initially determined that it was a problem with the database time zone setting.

Check time zone

Log in to the database to view the time zone configuration:

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | EDT |
| time_zone | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

• system_time_zone indicates that the time zone used by the system is EDT, which is Eastern Daylight Saving Time (-4h) in North America.
• time_zone indicates that MySQL uses the system's time zone. That is, if no time zone information is set when connecting, this time zone configuration will be used.

Change time zone

To change the time zone:

# Only change the time zone of the current session and stop the session from being invalidated set time_zone = '+8:00';

# Modify the global time zone configuration set global time_zone = '+8:00';
flush privileges;

Of course, you can also modify the configuration file (my.cnf) to achieve the configuration, but you need to restart the service.

# vim /etc/my.cnf ##Add default-time_zone = '+8:00' in the [mysqld] area
# /etc/init.d/mysqld restart ##Restart mysql to make the new time zone take effect

By the way, unlike China, the United States has 4 time zones...

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.