如何设置MySQL的时区？

本文翻译自：How do I set the time zone of MySQL?

On one server, when I run: 在一台服务器上，当我运行时：

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2009-05-30 16:54:29 |
+---------------------+
1 row in set (0.00 sec)

On another server: 在另一台服务器上：

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2009-05-30 20:01:43 |
+---------------------+
1 row in set (0.00 sec)

---

#1楼

参考：https://stackoom.com/question/3uAW/如何设置MySQL的时区

---

#2楼

If you are using the MySql Workbench you can set this by opening up the administrator view and select the Advanced tab. 如果使用的是MySql Workbench ，则可以通过打开管理员视图并选择“高级”选项卡来进行设置。 The top section is "Localization" and the first check box should be "default-time-zone". 顶部是“本地化”，第一个复选框应该是“默认时区”。 Check that box and then enter your desired time zone, restart the server and you should be good to go. 选中该框，然后输入所需的时区，重新启动服务器，您应该一切顺利。

---

#3楼

Simply run this on your MySQL server: 只需在您的MySQL服务器上运行此命令即可：

SET GLOBAL time_zone = '+8:00';

Where +8:00 will be your time zone. 其中+8：00将是您的时区。

---

#4楼

I thought this might be useful: 我认为这可能有用：

There are three places where the timezone might be set in MySQL: 在MySQL中可以在三个地方设置时区：

In the file "my.cnf" in the [mysqld] section 在[mysqld]部分的“ my.cnf”文件中

default-time-zone='+00:00'

@@global.time_zone variable @@ global.time_zone变量

To see what value they are set to: 要查看它们设置为什么值：

SELECT @@global.time_zone;

To set a value for it use either one: 要为其设置值，请使用以下任一方法：

SET GLOBAL time_zone = '+8:00';
SET GLOBAL time_zone = 'Europe/Helsinki';
SET @@global.time_zone = '+00:00';

(Using named timezones like 'Europe/Helsinki' means that you have to have a timezone table properly populated.) （使用诸如“ Europe / Helsinki”之类的时区意味着您必须正确填充时区表。）

Keep in mind that +02:00 is an offset. 请记住，+ +02:00是偏移量。 Europe/Berlin is a timezone (that has two offsets) and CEST is a clock time that corresponds to a specific offset. Europe/Berlin是时区（具有两个偏移量）， CEST是与特定偏移量相对应的时钟时间。

@@session.time_zone variable @@ session.time_zone变量

SELECT @@session.time_zone;

To set it use either one: 要设置它，请使用以下任一方法：

SET time_zone = 'Europe/Helsinki';
SET time_zone = "+00:00";
SET @@session.time_zone = "+00:00";

Both might return SYSTEM which means that they use the timezone set in my.cnf. 两者都可能返回SYSTEM，这意味着它们使用了my.cnf中设置的时区。

For timezone names to work, you must setup your timezone information tables need to be populated: http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html . 为了使时区名称起作用，必须设置时区信息表： http : //dev.mysql.com/doc/refman/5.1/en/time-zone-support.html 。 I also mention how to populate those tables in this answer . 我还提到了如何在此答案中填充这些表。

To get the current timezone offset as TIME 要将当前时区偏移量设为TIME

SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);

It will return 02:00:00 if your timezone is +2:00. 如果您的时区为+2：00，它将返回02:00:00。

To get the current UNIX timestamp: 要获取当前的UNIX时间戳：

SELECT UNIX_TIMESTAMP();
SELECT UNIX_TIMESTAMP(NOW());

To get the timestamp column as a UNIX timestamp 将timestamp列获取为UNIX时间戳

SELECT UNIX_TIMESTAMP(`timestamp`) FROM `table_name`

To get a UTC datetime column as a UNIX timestamp 获取UTC日期时间列作为UNIX时间戳记

SELECT UNIX_TIMESTAMP(CONVERT_TZ(`utc_datetime`, '+00:00', @@session.time_zone)) FROM `table_name`

Note: Changing the timezone will not change the stored datetime or timestamp , but it will show a different datetime for existing timestamp columns as they are internally stored as UTC timestamps and externally displayed in the current MySQL timezone. 注意：更改时区不会更改存储的datetime或timestamp ，但是它将为现有时间戳列显示不同的日期时间，因为它们在内部存储为UTC时间戳，并在外部显示在当前MySQL时区中。

I made a cheatsheet here: Should MySQL have its timezone set to UTC? 我在这里做了一个备忘单： MySQL是否应该将其时区设置为UTC？

---

#5楼

Keep in mind, that 'Country/Zone' is not working sometimes... This issue is not OS, MySQL version and hardware dependent - I've met it since FreeBSD 4 and Slackware Linux in year 2003 till today. 请记住，“国家/地区”有时无法使用...这个问题不依赖于操作系统，MySQL版本和硬件-从2003年FreeBSD 4和Slackware Linux到今天，我都遇到了这个问题。 MySQL from version 3 till latest source trunk. 从版本3到最新的源主干的MySQL。 It is ODD, but it DOES happens. 它是奇数，但确实会发生。 For example: 例如：

root@Ubuntu# ls -la /usr/share/zoneinfo/US
total 8

drwxr-xr-x  2 root root 4096 Apr 10  2013 .
drwxr-xr-x 22 root root 4096 Apr 10  2013 ..
lrwxrwxrwx  1 root root   18 Jul  8 22:33 Alaska -> ../SystemV/YST9YDT
lrwxrwxrwx  1 root root   21 Jul  8 22:33 Aleutian -> ../posix/America/Adak
lrwxrwxrwx  1 root root   15 Jul  8 22:33 Arizona -> ../SystemV/MST7
lrwxrwxrwx  1 root root   18 Jul  8 22:33 Central -> ../SystemV/CST6CDT
lrwxrwxrwx  1 root root   18 Jul  8 22:33 Eastern -> ../SystemV/EST5EDT
lrwxrwxrwx  1 root root   37 Jul  8 22:33 East-Indiana -> ../posix/America/Indiana/Indianapolis
lrwxrwxrwx  1 root root   19 Jul  8 22:33 Hawaii -> ../Pacific/Honolulu
lrwxrwxrwx  1 root root   24 Jul  8 22:33 Indiana-Starke -> ../posix/America/Knox_IN
lrwxrwxrwx  1 root root   24 Jul  8 22:33 Michigan -> ../posix/America/Detroit
lrwxrwxrwx  1 root root   18 Jul  8 22:33 Mountain -> ../SystemV/MST7MDT
lrwxrwxrwx  1 root root   18 Jul  8 22:33 Pacific -> ../SystemV/PST8PDT
lrwxrwxrwx  1 root root   18 Jul  8 22:33 Pacific-New -> ../SystemV/PST8PDT
lrwxrwxrwx  1 root root   20 Jul  8 22:33 Samoa -> ../Pacific/Pago_Pago
root@Ubuntu#

And a statement like that is supposed to work: 这样的语句应该起作用：

SET time_zone='US/Eastern';

But you have this problem: 但是你有这个问题：

Error Code: 1298. Unknown or incorrect time zone: 'EUS/Eastern' 错误代码：1298。未知或不正确的时区：'EUS / Eastern'

Take a look at the subfolder in your zone information directory, and see the ACTUAL filename for symlink, in this case it's EST5EDT. 查看区域信息目录中的子文件夹，并查看符号链接的ACTUAL文件名，在本例中为EST5EDT。 Then try this statement instead: 然后尝试使用以下语句：

SET time_zone='EST5EDT';

And it's actually working as it is supposed to! 它实际上正在按预期的方式工作！ :) Keep this trick in mind; :)记住这个技巧； I haven't seen it to be documented in MySQL manuals and official documentation. 我还没有看到它在MySQL手册和官方文档中有记录。 But reading the corresponding documentation is must-do thing: MySQL 5.5 timezone official documentation - and don't forget to load timezone data into your server just like that (run as root user!): 但是必须阅读相应的文档： MySQL 5.5时区官方文档 -并且不要忘记像那样将时区数据加载到服务器中（以root用户身份运行！）：

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Trick number one - it must be done exactly under MySQL root user. 第一招-必须完全在MySQL root用户下完成。 It can fail or produce non-working result even from the user that has full access to a MySQL database - I saw the glitch myself. 即使是对MySQL数据库具有完全访问权限的用户，它也可能失败或产生无法正常工作的结果-我自己也看到了故障。

---

#6楼

For anyone still having this issue: 对于仍然有此问题的任何人：

value="jdbc:mysql://localhost:3306/dbname?serverTimezone=UTC"

Worked for me. 为我工作。 Just append ?serverTimezone=UTC at the end. 只需在末尾附加?serverTimezone=UTC 。