如何设置 MySQL 的时区？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/930900/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I set the time zone of MySQL?
提问by omg
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)
回答by Timo Huovinen
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.)
（使用诸如“欧洲/赫尔辛基”之类的命名时区意味着您必须正确填充时区表。）
Keep in mind that +02:00is an offset. Europe/Berlinis a timezone (that has two offsets) and CESTis a clock time that corresponds to a specific offset.
请记住，这+02:00是一个偏移量。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. I also mention how to populate those tables in this answer.
要使时区名称起作用，您必须设置需要填充的时区信息表：http: //dev.mysql.com/doc/refman/5.1/en/time-zone-support.html。我还在这个答案中提到了如何填充这些表。
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
获取时间戳列作为 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.
注意：更改时区不会更改存储的日期时间或时间戳，但它会为现有时间戳列显示不同的日期时间，因为它们在内部存储为 UTC 时间戳并在当前 MySQL 时区中显示在外部。
I made a cheatsheet here: Should MySQL have its timezone set to UTC?
我在这里做了一个备忘单：MySQL 是否应该将时区设置为 UTC？
回答by Noel Murphy
For anyone still having this issue:
对于仍然有此问题的任何人：
value="jdbc:mysql://localhost:3306/dbname?serverTimezone=UTC"
Worked for me. Just append ?serverTimezone=UTCat the end.
为我工作。只?serverTimezone=UTC在最后追加。
回答by James Skidmore
To set it for the current session, do:
要为当前会话设置它，请执行以下操作：
SET time_zone = timezonename;
回答by jane
When you can configure the time zone server for MySQL or PHP:
何时可以为 MySQL 或 PHP 配置时区服务器：
Remember:
记住：
Change timezone system. Example for Ubuntu:
$ sudo dpkg-reconfigure tzdataRestart the server or you can restart Apache 2 and MySQL:
/etc/init.d/mysql restart
更改时区系统。Ubuntu 示例：
$ sudo dpkg-reconfigure tzdata重新启动服务器，或者您可以重新启动 Apache 2 和 MySQL：
/etc/init.d/mysql restart
回答by Rico Chan
Simply run this on your MySQL server:
只需在您的 MySQL 服务器上运行它：
SET GLOBAL time_zone = '+8:00';
Where +8:00 will be your time zone.
其中 +8:00 将是您的时区。
回答by Swapnil Bijwe
This work for me for a location in India:
这对我在印度的一个地点工作：
SET GLOBAL time_zone = "Asia/Calcutta";
SET time_zone = "+05:30";
SET @@session.time_zone = "+05:30";
回答by Alex Martelli
You can specify the server's default timezone when you start it, see http://dev.mysql.com/doc/refman/5.1/en/server-options.htmland specifically the --default-time-zone=timezoneoption. You can check the global and session time zones with
您可以在启动时指定服务器的默认时区，请参阅http://dev.mysql.com/doc/refman/5.1/en/server-options.html和具体--default-time-zone=timezone选项。您可以检查全局和会话时区
SELECT @@global.time_zone, @@session.time_zone;
set either or both with the SETstatement, &c; see http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.htmlfor many more details.
用SET语句设置一个或两个，&c; 有关更多详细信息，请参阅http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html。
回答by Alexey Vesnin
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 from version 3 till latest source trunk. It is ODD, but it DOES happens. For example:
请记住，“国家/地区”有时不起作用......这个问题不依赖于操作系统、MySQL 版本和硬件 - 从 2003 年的 FreeBSD 4 和 Slackware Linux 到今天，我已经遇到了它。MySQL 从版本 3 到最新的源主干。这是奇怪的，但它确实发生了。例如：
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. Then try this statement instead:
查看区域信息目录中的子文件夹，并查看符号链接的实际文件名，在本例中为 EST5EDT。然后试试这个语句：
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. 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 手册和官方文档中。但是阅读相应的文档是必须做的事情：MySQL 5.5 timezone 官方文档- 不要忘记将时区数据加载到您的服务器中（以 root 用户身份运行！）：
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
Trick number one - it must be done exactly under MySQL root user. 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 root 用户下完成。即使是对 MySQL 数据库具有完全访问权限的用户，它也可能失败或产生非工作结果 - 我自己也看到了故障。
回答by MeKoo Solutions
If you're using PDO:
如果您使用的是 PDO：
$offset="+10:00";
$db->exec("SET time_zone='".$offset."';");
If you're using MySQLi:
如果您使用的是 MySQLi：
$db->MySQLi->query("SET time_zone='".$offset."';");
More about formatting the offset here: https://www.sitepoint.com/synchronize-php-mysql-timezone-configuration/
有关在此处格式化偏移量的更多信息：https: //www.sitepoint.com/synchronize-php-mysql-timezone-configuration/
回答by hamza saber
This is a 10 years old question, but anyway here's what worked for me. I'm using MySQL 8.0 with Hibernate 5 and SpringBoot 4.
这是一个 10 年前的问题，但无论如何这对我有用。我在 Hibernate 5 和 SpringBoot 4 中使用 MySQL 8.0。
I've tried the above accepted answer but didn't work for me, what worked for me is this:
我已经尝试了上述接受的答案，但对我不起作用，对我有用的是：
db.url=jdbc:mysql://localhost:3306/testdb?useSSL=false&serverTimezone=Europe/Warsaw
If this helps you don't forget to upvote it :D
如果这对您有帮助，请不要忘记给它点赞：D
