MySQL是否应将其时区设置为UTC?

wooyq4lh  于 2022-11-28  发布在  Mysql
关注(0)|答案(6)|浏览(169)

https://serverfault.com/questions/191331/should-servers-have-their-timezone-set-to-gmt-utc的跟进问题
MySQL时区应该设为UTC还是应该设为与服务器或PHP相同的时区?(如果不是UTC)
有哪些利弊?

4ngedf3f

4ngedf3f1#

看起来服务器上的时区并不重要,只要为当前时区正确设置了时间,知道存储的日期时间列的时区,并了解夏令时的问题。
另一方面,如果您可以控制所使用的服务器的时区,那么您可以在内部将所有内容设置为UTC,而不必担心时区和DST,至少在存储内部时间时是这样。
以下是我收集的一些笔记,内容是如何将时区作为备忘单的一种形式,供自己和其他人使用,这些备忘单可能会影响用户为他/她的服务器选择什么时区以及他/她将如何存储日期和时间。

MySQL时区速查表

备注:
1.更改时区不会更改存储的日期时间或时间戳,但会从时间戳列中选择不同的日期时间
1.**警告!**UTC有闰秒,看起来像'2012-06-30 23:59:60',由于地球自转变慢,可以随机添加,提前6个月通知

  1. GMT会混淆秒,这就是UTC被发明的原因。
    1.**警告!**由于夏时制,不同的地区时区可能会产生相同的日期时间值
    1.由于a limitation,时间戳记数据行只支援1970-01-01 00:00:01到2038-01-19 03:14:07 UTC的日期。
    1.在内部,MySQL timestamp column存储为UTC,但在选择日期时,MySQL会自动将其转换为当前会话时区。
    在时间戳中存储日期时,MySQL将假定该日期在当前会话时区中,并将其转换为UTC进行存储。
  2. MySQL可以在日期时间列中存储部分日期,这些日期看起来像“2013-00-00 04:00:00”
    1.如果您将日期时间列设置为NULL,MySQL将存储“0000-00 -00 00:00:00”,除非您在创建该列时特别将其设置为允许空值。
  3. Read this

选择UTC格式的时间戳列

无论当前MySQL会话处于哪个时区:

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

您也可以将服务器或全局或当前会话时区设置为UTC,然后选择时间戳,如下所示:

SELECT `timestamp_field` FROM `table_name`

选择UTC中的当前日期时间:

SELECT UTC_TIMESTAMP();
SELECT UTC_TIMESTAMP;
SELECT CONVERT_TZ(NOW(), @@session.time_zone, '+00:00');

示例结果: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。

要获取当前UNIX时间戳(以秒为单位):

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

获取时间戳列作为UNIX时间戳

SELECT UNIX_TIMESTAMP(`timestamp`) FROM `table_name`

获取UTC日期时间列作为UNIX时间戳

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

从正的UNIX时间戳整数获取当前时区日期时间

SELECT FROM_UNIXTIME(`unix_timestamp_int`) FROM `table_name`

从UNIX时间戳获取UTC日期时间

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

从负UNIX时间戳整数获取当前时区日期时间

SELECT DATE_ADD('1970-01-01 00:00:00',INTERVAL -957632400 SECOND)

在MySQL中有三个地方可以设置时区:

注意:时区可以设置为两种格式:

  1. UTC的偏移量:“+00:00”、“+10:00”或“-6:00”
    1.作为命名时区:“欧洲/赫尔辛基”、“美国/东部”或“MET”
    只有在mysql数据库中创建并填充了时区信息表后,才能使用命名时区。

在文件“my.cnf”中

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 variable”和“@@session.time_zone variable”都可能返回“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`

设置mysql时区表

要使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

(make确保您的服务器dst规则是最新的zdump -v Europe/Moscow | grep 2011https://chrisjean.com/updating-daylight-saving-time-on-linux/

查看每个时区的完整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_TZ还根据上表中的规则和您使用的日期应用任何必要的DST更改。

备注:

根据docs,您为time_zone设置的值不会更改,例如,如果您将其设置为“+01:00”,则time_zone将设置为UTC的偏移量,它不遵循DST,因此它将全年保持不变。
只有名为timezones的会在夏令时期间更改时间。
缩写如CET将始终是冬季时间,CEST将是夏季时间,而+01:00将始终是UTC时间+1小时,两者都不会随DST而更改。

system时区将是安装了mysql的主机的时区(除非mysql无法确定它)
您可以阅读有关使用DST here的详细信息
何时不使用UTC由传奇人物Jon Skeet介绍:https://codeblog.jonskeet.uk/2019/03/27/storing-utc-is-not-a-silver-bullet/(例如,表示时间而非瞬间的未来计划事件)
相关问题:

资料来源:

sycxhyv7

sycxhyv72#

PHP和MySQL有自己的默认时区配置。您应该在数据库和Web应用程序之间同步时间,否则可能会出现一些问题。
阅读本教程:How To Synchronize Your PHP and MySQL Timezones

owfi6suc

owfi6suc3#

这是一个工作示例:

jdbc:mysql://localhost:3306/database?useUnicode=yes&characterEncoding=UTF-8&serverTimezone=Europe/Moscow
ua4mk5z4

ua4mk5z44#

利弊几乎是一样的,这取决于你想不想这样。
注意,如果MySQL时区与您的系统时间不同(例如PHP),比较时间或打印给用户将涉及一些修修补补。

jpfvwuh4

jpfvwuh45#

让你的应用程序不受服务器时区的影响怎么样?
由于以下任何一种可能的情况:

  • 您可能无法控制Web/数据库服务器的时区设置
  • 你可能会搞砸并设置错误
  • 如其他答案中所述,有太多的设置,需要跟踪的内容太多,您可能会遗漏一些内容
  • 服务器上的更新、软件重置或其他管理员可能会在不知情的情况下将服务器的时区重置为默认值,从而破坏您的应用程序

以上所有情况给予导致应用程序的时间计算中断。因此,似乎更好的方法是使应用程序独立于服务器的时区工作。
其思想很简单,在将日期存储到数据库之前,总是以UTC格式创建日期,并且总是从以UTC格式存储的值重新创建日期。这样,时间计算就不会出错,因为它们总是以UTC格式。这可以通过在创建PHP DateTime对象时显式声明DateTimeZone参数来实现。
另一方面,客户端功能可以配置为将从服务器接收到的所有日期/时间转换为客户端的时区。moment.js等库使这一操作变得非常简单。
例如,在数据库中存储日期时,不使用MySQL得NOW()函数,而是按如下所示创建UTC格式得时间戳字符串:

// Storing dates
$date = new DateTime('now', new DateTimeZone('UTC'));
$sql = 'insert into table_name (date_column) values ("' . $date . '")';

// Retreiving dates
$sql = 'select date_column from table_name where condition';
$dateInUTC = new DateTime($date_string_from_db, new DateTimeZone('UTC'));

您可以在PHP中为所有创建的日期设置默认时区,这样就不必在每次创建日期时都初始化DateTimeZone类。

jogvjijk

jogvjijk6#

检查时区

SELECT @@系统时区为System_tz,@@全球时区为MYSQL时区,NOW()为mysql的时间。
设置全球时区= '+05:30';

更改系统时区,然后重新启动MariaDB/MYSQL

并再次检查时区

相关问题