https://serverfault.com/questions/191331/should-servers-have-their-timezone- set-to-gmt-utc的后续问题
MySQL 时区应该设置为 UTC 还是应该设置为与服务器或 PHP 设置相同的时区?(如果不是UTC)
优缺点都有什么?
只要您为当前时区设置了正确的时间,知道您存储的日期时间列的时区,并且知道夏令时的问题,服务器上的时区似乎并不重要。
另一方面,如果您可以控制所使用服务器的时区,那么您可以在内部将所有内容设置为 UTC,而不必担心时区和 DST,至少在存储内部时间方面是这样。
以下是我收集的一些关于如何使用时区作为我自己和其他人的备忘单形式的笔记,这可能会影响人们将为他/她的服务器选择哪个时区以及他/她将如何存储日期和时间。
笔记:
更改时区 不会更改存储的日期时间或时间戳 ,但会从时间戳列中选择不同的日期时间
警告! 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 可以在日期时间列中存储部分日期,这些看起来像“2013-00-00 04:00:00”
如果您将日期时间列设置为 NULL,MySQL 将存储“0000-00-00 00:00:00”,除非您在创建该列时专门设置该列以允许为空。
无论当前 MySQL 会话在哪个时区:
SELECT CONVERT_TZ(`timestamp_field`, @@session.time_zone, '+00:00') AS `utc_datetime` FROM `table_name`
您还可以将服务器或全局或当前会话时区设置为 UTC,然后选择时间戳,如下所示:
SELECT `timestamp_field` FROM `table_name`
SELECT UTC_TIMESTAMP(); SELECT UTC_TIMESTAMP; SELECT CONVERT_TZ(NOW(), @@session.time_zone, '+00:00');
示例结果:2015-03-24 17:02:41
2015-03-24 17:02:41
SELECT NOW(); SELECT CURRENT_TIMESTAMP; SELECT CURRENT_TIMESTAMP();
SELECT @@system_time_zone;
返回莫斯科时间的“MSK”或“+04:00”,例如,存在(或曾经)一个 MySQL 错误,如果设置为数字偏移量,它将不会调整夏令时
SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);
如果您的时区是 +2:00,它将返回 02:00:00。
SELECT UNIX_TIMESTAMP(NOW()); SELECT UNIX_TIMESTAMP();
SELECT UNIX_TIMESTAMP(`timestamp`) FROM `table_name`
SELECT UNIX_TIMESTAMP(CONVERT_TZ(`utc_datetime`, '+00:00', @@session.time_zone)) FROM `table_name`
SELECT FROM_UNIXTIME(`unix_timestamp_int`) FROM `table_name`
SELECT CONVERT_TZ(FROM_UNIXTIME(`unix_timestamp_int`), @@session.time_zone, '+00:00') FROM `table_name`
SELECT DATE_ADD('1970-01-01 00:00:00',INTERVAL -957632400 SECOND)
注意:时区可以设置为 2 种格式:
只有在 mysql 数据库中的时区信息表已创建并填充后,才能使用命名时区。
default_time_zone='+00:00'
或者
timezone='UTC'
查看它们设置为什么值
SELECT @@global.time_zone;
要为其设置值,请使用以下任一:
SET GLOBAL time_zone = '+8:00'; SET GLOBAL time_zone = 'Europe/Helsinki'; SET @@global.time_zone='+00:00';
SELECT @@session.time_zone;
要设置它,请使用以下任一:
SET time_zone = 'Europe/Helsinki'; SET time_zone = "+00:00"; SET @@session.time_zone = "+00:00";
“@@global.time_zone 变量”和“@@session.time_zone 变量”都可能返回“SYSTEM”,这意味着它们使用“my.cnf”中设置的时区。
为了使时区名称起作用(即使是默认时区),您必须设置您的时区信息表,需要填充: http://dev.mysql.com/doc/refman/5.1/en/time-zone-support。 html
注意:你不能这样做,因为它会返回 NULL:
SELECT CONVERT_TZ(`timestamp_field`, TIMEDIFF(NOW(), UTC_TIMESTAMP), '+00:00') AS `utc_datetime` FROM `table_name`
为了CONVERT_TZ工作,您需要填充时区表
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` ;
如果它们是空的,则通过运行此命令来填充它们
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
如果此命令给出错误“ data too long for column ‘abbreviation’ at row 1 ”,则可能是由于在时区缩写末尾附加了 NULL 字符
修复是运行这个
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
(确保您的服务器 dst 规则是最新的zdump -v Europe/Moscow | grep 2011 https://chrisjean.com/updating-daylight-saving-time-on- linux/)
zdump -v Europe/Moscow | grep 2011
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_TZ还根据上表中的规则和您使用的日期应用任何必要的 DST 更改。
注意: 根据文档,您为 time_zone 设置的值不会改变,例如,如果您将其设置为“+01:00”,那么 time_zone 将设置为与 UTC 的偏移量,它不遵循 DST,所以它将全年保持不变。
只有指定的时区会在夏令时更改时间。
像这样的缩写CET将始终是冬季时间和CEST夏季时间,而 +01:00 将始终是UTC时间 + 1 小时,两者都不会随 DST 改变。
CET
CEST
UTC
system时区将是安装 mysql 的主机的时区(除非 mysql 无法确定它)
system
您可以在此处阅读有关使用 DST 的更多信息
传奇人物 Jon Skeet 何时不使用 UTC:https ://codeblog.jonskeet.uk/2019/03/27/storing-utc-is-not-a- silver-bullet/ (例如未来的预定活动代表一个时间,而不是一个瞬间)