MySQL 是否应该将其时区设置为 UTC？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19023978/
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
Should MySQL have its timezone set to UTC?
提问by Timo Huovinen
Follow up question of https://serverfault.com/questions/191331/should-servers-have-their-timezone-set-to-gmt-utc
跟进https://serverfault.com/questions/191331/should-servers-have-their-timezone-set-to-gmt-utc 的问题
Should the MySQL timezone be set to UTC or should it be set to be the same timezone as the server or PHP is set? (If it is not UTC)
MySQL 时区应该设置为 UTC 还是应该设置为与服务器或 PHP 设置的时区相同？（如果不是UTC）
What are the pros and cons?
优缺点都有什么？
回答by Timo Huovinen
It seems that it does not matter what timezone is on the server as long as you have the time set right for the current timezone, know the timezone of the datetime columns that you store, and are aware of the issues with daylight savings time.
只要您为当前时区设置了正确的时间，知道您存储的日期时间列的时区，并且知道夏令时的问题，服务器上的时区似乎并不重要。
On the other hand if you have control of the timezones of the servers you work with then you can have everything set to UTC internally and never worry about timezones and DST.
另一方面，如果您可以控制所使用服务器的时区，那么您可以在内部将所有内容设置为 UTC，而不必担心时区和 DST。
Here are some notes I collected of how to work with timezones as a form of cheatsheet for myself and others which might influence what timezone the person will choose for his/her server and how he/she will store date and time.
以下是我收集的一些关于如何使用时区作为我自己和其他人的备忘单形式的笔记，这些笔记可能会影响该人为其服务器选择的时区以及他/她将如何存储日期和时间。
MySQL Timezone Cheatsheet
MySQL 时区备忘单
Notes:
笔记：
- Changing the timezone will not change the stored datetime or timestamp, but it will select a different datetime from timestamp columns
- Warning!UTC has leap seconds, these look like '2012-06-30 23:59:60' and can be added randomly, with 6 months prior notice, due to the slowing of the earths rotation
GMT confuses seconds, which is why UTC was invented.
Warning!different regional timezones might produce the same datetime value due to daylight savings time
- The timestamp column only supports dates 1970-01-01 00:00:01 to 2038-01-19 03:14:07 UTC, due to a limitation.
Internally a MySQL timestamp columnis stored as UTCbut when selecting a date MySQL will automatically convert it to the current session timezone.
When storing a date in a timestamp, MySQL will assume that the date is in the current session timezone and convert it to UTC for storage.
- MySQL can store partial dates in datetime columns, these look like "2013-00-00 04:00:00"
- MySQL stores "0000-00-00 00:00:00" if you set a datetime column as NULL, unless you specifically set the column to allow null when you create it.
- Read this
- 更改时区不会更改存储的日期时间或时间戳，但会从时间戳列中选择不同的日期时间
- 警告！UTC 有闰秒，这些看起来像 '2012-06-30 23:59:60' 并且可以随机添加，提前 6 个月通知，由于地球自转变慢
GMT 混淆了秒，这就是发明 UTC 的原因。
警告！由于夏令时，不同的区域时区可能会产生相同的日期时间值
- 由于限制，时间戳列仅支持日期 1970-01-01 00:00:01 到 2038-01-19 03:14:07 UTC 。
在内部，MySQL 时间戳列存储为UTC，但在选择日期时，MySQL 会自动将其转换为当前会话时区。
在时间戳中存储日期时，MySQL 会假定该日期在当前会话时区中，并将其转换为 UTC 进行存储。
- MySQL 可以在 datetime 列中存储部分日期，这些看起来像“2013-00-00 04:00:00”
- 如果您将日期时间列设置为 NULL，MySQL 将存储“0000-00-00 00:00:00”，除非您在创建时专门将该列设置为允许为空。
- 读这个
To select a timestamp column in UTC format
选择 UTC 格式的时间戳列
no matter what timezone the current MySQL session is in:
无论当前 MySQL 会话处于哪个时区：
SELECT
CONVERT_TZ(`timestamp_field`, @@session.time_zone, '+00:00') AS `utc_datetime`
FROM `table_name`
You can also set the sever or global or current session timezone to UTC and then select the timestamp like so:
您还可以将服务器或全局或当前会话时区设置为 UTC，然后像这样选择时间戳：
SELECT `timestamp_field` FROM `table_name`
To select the current datetime in UTC:
要选择 UTC 中的当前日期时间：
SELECT UTC_TIMESTAMP();
SELECT UTC_TIMESTAMP;
SELECT CONVERT_TZ(NOW(), @@session.time_zone, '+00:00');
Example result: 2015-03-24 17:02:41
结果示例： 2015-03-24 17:02:41
To select the current datetime in the session timezone
在会话时区中选择当前日期时间
SELECT NOW();
SELECT CURRENT_TIMESTAMP;
SELECT CURRENT_TIMESTAMP();
To select the timezone that was set when the server launched
选择服务器启动时设置的时区
SELECT @@system_time_zone;
Returns "MSK" or "+04:00" for Moscow time for example, there is (or was) a MySQL bug where if set to a numerical offset it would not adjust the Daylight savings time
例如，返回莫斯科时间的“MSK”或“+04:00”，存在（或曾经）一个 MySQL 错误，如果设置为数字偏移，则不会调整夏令时
To get the current timezone
获取当前时区
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 (in seconds):
获取当前的 UNIX 时间戳（以秒为单位）：
SELECT UNIX_TIMESTAMP(NOW());
SELECT UNIX_TIMESTAMP();
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`
Get a current timezone datetime from a positive UNIX timestamp integer
从正的 UNIX 时间戳整数获取当前时区日期时间
SELECT FROM_UNIXTIME(`unix_timestamp_int`) FROM `table_name`
Get a UTC datetime from a UNIX timestamp
从 UNIX 时间戳中获取 UTC 日期时间
SELECT CONVERT_TZ(FROM_UNIXTIME(`unix_timestamp_int`), @@session.time_zone, '+00:00')
FROM `table_name`
Get a current timezone datetime from a negative UNIX timestamp integer
从负的 UNIX 时间戳整数获取当前时区日期时间
SELECT DATE_ADD('1970-01-01 00:00:00',INTERVAL -957632400 SECOND)
There are 3 places where the timezone might be set in MySQL:
在 MySQL 中可以在 3 个地方设置时区：
Note: A timezone can be set in 2 formats:
注意：时区可以设置为 2 种格式：
- an offset from UTC: '+00:00', '+10:00' or '-6:00'
- as a named time zone: 'Europe/Helsinki', 'US/Eastern', or 'MET'
- UTC 的偏移量：“+00:00”、“+10:00”或“-6:00”
- 作为命名时区：“Europe/Helsinki”、“US/Eastern”或“MET”
Named time zones can be used only if the time zone information tables in the mysql database have been created and populated.
只有在 mysql 数据库中的时区信息表已经创建并填充后，才能使用命名时区。
in the file "my.cnf"
在文件“my.cnf”中
default_time_zone='+00:00'
or
或者
timezone='UTC'
@@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';
@@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 "@@global.time_zone variable" and "@@session.time_zone variable" might return "SYSTEM" which means that they use the timezone set in "my.cnf".
“@@global.time_zone 变量”和“@@session.time_zone 变量”都可能返回“SYSTEM”，这意味着它们使用“my.cnf”中设置的时区。
For timezone names to work (even for default-time-zone) 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
Note: you can not do this as it will return NULL:
注意：您不能这样做，因为它会返回 NULL：
SELECT
CONVERT_TZ(`timestamp_field`, TIMEDIFF(NOW(), UTC_TIMESTAMP), '+00:00') AS `utc_datetime`
FROM `table_name`
Setup mysql timezone tables
设置mysql时区表
For CONVERT_TZto work, you need the timezone tables to be populated
为了CONVERT_TZ工作，您需要填充时区表
SELECT * FROM mysql.`time_zone` ;
SELECT * FROM mysql.`time_zone_leap_second` ;
SELECT * FROM mysql.`time_zone_name` ;
SELECT * FROM mysql.`time_zone_transition` ;
SELECT * FROM mysql.`time_zone_transition_type` ;
If they are empty, then fill them up by running this command
如果它们是空的，则通过运行此命令将它们填满
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
if this command gives you the error "data too long for column 'abbreviation' at row 1", then it might be caused by a NULL character being appended at the end of the timezone abbreviation
如果此命令给您错误“第 1 行‘缩写’列的数据太长”，则可能是由于在时区缩写末尾附加了 NULL 字符所致
the fix being to run this
修复是运行这个
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
(if the above gives error "data too long for column 'abbreviation' at row 1")
mysql_tzinfo_to_sql /usr/share/zoneinfo > /tmp/zut.sql
echo "SET SESSION SQL_MODE = '';" > /tmp/mysql_tzinfo_to.sql
cat /tmp/zut.sql >> /tmp/mysql_tzinfo_to.sql
mysql --defaults-file=/etc/mysql/my.cnf --user=verifiedscratch -p mysql < /tmp/mysql_tzinfo_to.sql
(make sure your servers dst rules are up to date zdump -v Europe/Moscow | grep 2011https://chrisjean.com/updating-daylight-saving-time-on-linux/)
（确保您的服务器 dst 规则是最新的zdump -v Europe/Moscow | grep 2011https://chrisjean.com/updating-daylight-saving-time-on-linux/）
See the full DST (Daylight Saving Time) transition history for every timezone
查看每个时区的完整 DST（夏令时）转换历史
SELECT
tzn.Name AS tz_name,
tztt.Abbreviation AS tz_abbr,
tztt.Is_DST AS is_dst,
tztt.`Offset` AS `offset`,
DATE_ADD('1970-01-01 00:00:00',INTERVAL tzt.Transition_time SECOND) AS transition_date
FROM mysql.`time_zone_transition` tzt
INNER JOIN mysql.`time_zone_transition_type` tztt USING(Time_zone_id, Transition_type_id)
INNER JOIN mysql.`time_zone_name` tzn USING(Time_zone_id)
-- WHERE tzn.Name LIKE 'Europe/Moscow' -- Moscow has weird DST changes
ORDER BY tzt.Transition_time ASC
CONVERT_TZalso applies any necessary DST changes based on the rules in the above tables and the date that you use.
CONVERT_TZ还根据上表中的规则和您使用的日期应用任何必要的 DST 更改。
Note:
According to the docs, the value you set for time_zone does not change, if you set it as "+01:00" for example, then the time_zone will be set as an offset from UTC, which does not follow DST, so it will stay the same all year round.
注意：
根据文档，您为 time_zone 设置的值不会改变，例如，如果您将其设置为“+01:00”，则 time_zone 将设置为与 UTC 的偏移量，不遵循 DST，因此它将全年保持不变。
Only the named timezoneswill change time during daylight savings time.
Abbreviations like CETwill always be a winter time and CESTwill be summer time while +01:00 will always be UTCtime + 1 hour and both won't change with DST.
像这样的缩写CET永远是冬季时间和CEST夏季时间，而 +01:00 永远是UTC时间 + 1 小时，两者都不会随着夏令时而改变。
The systemtimezone will be the timezone of the host machine where mysql is installed (unless mysql fails to determine it)
的system时区将被安装的MySQL，其中主机的时区（除非MySQL的失败来确定它）
You can read more about working with DST here
您可以在此处阅读有关使用 DST 的更多信息
related questions:
相关问题：
- How do I set the time zone of MySQL?
- MySql - SELECT TimeStamp Column in UTC format
- How to get Unix timestamp in MySQL from UTC time?
- Converting Server MySQL TimeStamp To UTC
- https://dba.stackexchange.com/questions/20217/mysql-set-utc-time-as-default-timestamp
- How do I get the current time zone of MySQL?
- MySQL datetime fields and daylight savings time -- how do I reference the "extra" hour?
- Converting negative values from FROM_UNIXTIME
- 如何设置 MySQL 的时区？
- MySql - 选择 UTC 格式的时间戳列
- 如何从 UTC 时间获取 MySQL 中的 Unix 时间戳？
- 将服务器 MySQL 时间戳转换为 UTC
- https://dba.stackexchange.com/questions/20217/mysql-set-utc-time-as-default-timestamp
- 如何获取 MySQL 的当前时区？
- MySQL 日期时间字段和夏令时——如何引用“额外”小时？
- 从 FROM_UNIXTIME 转换负值
Sources:
资料来源：
- https://bugs.mysql.com/bug.php?id=68861
- http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
- http://dev.mysql.com/doc/refman/5.1/en/datetime.html
- http://en.wikipedia.org/wiki/Coordinated_Universal_Time
- http://shafiqissani.wordpress.com/2010/09/30/how-to-get-the-current-epoch-time-unix-timestamp/
- https://web.ivy.net/~carton/rant/MySQL-timezones.txt
- https://bugs.mysql.com/bug.php?id=68861
- http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
- http://dev.mysql.com/doc/refman/5.1/en/datetime.html
- http://en.wikipedia.org/wiki/Coordinated_Universal_Time
- http://shafiqissani.wordpress.com/2010/09/30/how-to-get-the-current-epoch-time-unix-timestamp/
- https://web.ivy.net/~carton/rant/MySQL-timezones.txt
回答by tatka
This is a working example:
这是一个工作示例：
jdbc:mysql://localhost:3306/database?useUnicode=yes&characterEncoding=UTF-8&serverTimezone=Europe/Moscow
回答by tatka
PHP and MySQL have their own default timezone configurations. You should synchronize time between your data base and web application, otherwise you could run some issues.
PHP 和 MySQL 有自己的默认时区配置。您应该在数据库和 Web 应用程序之间同步时间，否则可能会运行一些问题。
Read this tutorial: How To Synchronize Your PHP and MySQL Timezones
阅读本教程：如何同步 PHP 和 MySQL 时区
回答by alandarev
The pros and cons are pretty much identical.It depends on whether you want this or not.
优点和缺点几乎相同。这取决于你是否想要这个。
Be careful, if MySQL timezone differs from your system time (for instance PHP), comparing the time or printing to the user will involve some tinkering.
请注意，如果 MySQL 时区与您的系统时间（例如 PHP）不同，则将时间或打印与用户进行比较将涉及一些修补工作。
