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

时间：2023-08-17

本文介绍了MySQL 是否应该将其时区设置为 UTC?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

跟进问题https://serverfault.com/questions/191331/should-服务器有他们的时区设置到 gmt-utc

MySQL 时区应该设置为 UTC 还是应该设置为与服务器或 PHP 设置的时区相同?(如果不是UTC)

优缺点是什么?

解决方案

似乎服务器上的时区无关紧要，只要您将时间设置为当前时区，知道日期时间的时区您存储的列，并了解夏令时的问题.

另一方面，如果您可以控制所使用服务器的时区，那么您可以在内部将所有内容设置为 UTC，而不必担心时区和夏令时.

以下是我收集的关于如何使用时区作为我自己和其他人的备忘单形式的一些笔记，这些笔记可能会影响该人为其服务器选择的时区以及他/她将如何存储日期和时间.

MySQL 时区备忘单

注意事项:

改变时区不会改变存储的日期时间或时间戳，但它会从中选择不同的日期时间时间戳列
警告！ UTC 有闰秒，这些看起来像2012-06-30 23:59:60"并且可以随机添加，提前 6 个月通知，由于速度减慢地球自转
GMT 会混淆秒，这就是发明 UTC 的原因.
警告！不同的区域时区可能会产生相同的日期时间值到夏令时
由于限制.
在内部存储一个 MySQL 时间戳列作为 UTC 但选择日期时，MySQL 会自动将其转换为当前会话时区.
在时间戳中存储日期时，MySQL 将假定日期处于当前会话时区并将其转换为 UTC存储.
MySQL 可以在 datetime 列中存储部分日期，这些看起来像2013-00-00 04:00:00"
MySQL 存储0000-00-00 00:00:00"；如果您将日期时间列设置为NULL，除非您特别设置该列以在您创建它.
阅读本文

选择UTC格式的时间戳列

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

SELECTCONVERT_TZ(`timestamp_field`, @@session.time_zone, '+00:00') AS `utc_datetime`从`table_name`

您还可以将服务器或全局或当前会话时区设置为 UTC，然后像这样选择时间戳:

SELECT `timestamp_field` FROM `table_name`

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

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

示例结果:2015-03-24 17:02:41

在会话时区中选择当前日期时间

现在选择();选择 CURRENT_TIMESTAMP;选择 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());选择 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')从`table_name`

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

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

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

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

与 UTC 的偏移量:+00:00"、+10:00"或-6:00"
作为命名时区:Europe/Helsinki"、US/Eastern"或MET"

<块引用>

命名时区只有在时区信息表mysql 数据库中已经创建并填充.

在文件my.cnf"中

default_time_zone='+00:00'

或

timezone='UTC'

@@global.time_zone 变量

查看它们设置的值

SELECT @@global.time_zone;

要为其设置一个值，请使用其中之一:

SET GLOBAL time_zone = '+8:00';SET GLOBAL time_zone = '欧洲/赫尔辛基';SET @@global.time_zone='+00:00';

@@session.time_zone 变量

SELECT @@session.time_zone;

要设置它，请使用其中之一:

SET time_zone = '欧洲/赫尔辛基';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:

SELECTCONVERT_TZ(`timestamp_field`, TIMEDIFF(NOW(), UTC_TIMESTAMP), '+00:00') AS `utc_datetime`从`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 根 -p mysql

如果这个命令给你错误data too long for column 'abbreviation' at第 1 行"，则可能是由于在时区缩写末尾附加了 NULL 字符所致

修复是运行这个

mysql_tzinfo_to_sql/usr/share/zoneinfo |mysql -u 根 -p mysql(如果上面给出错误第 1 行‘缩写’列的数据太长")mysql_tzinfo_to_sql/usr/share/zoneinfo >/tmp/zut.sqlecho "SET SESSION SQL_MODE = '';";>/tmp/mysql_tzinfo_to.sqlcat/tmp/zut.sql >>/tmp/mysql_tzinfo_to.sqlmysql --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/)

查看每个时区的完整 DST(夏令时)转换历史

SELECTtzn.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从 mysql.`time_zone_transition` tztINNER 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' -- 莫斯科有奇怪的夏令时变化ORDER BY tzt.Transition_time ASC

CONVERT_TZ 还会根据上表中的规则和您使用的日期应用任何必要的 DST 更改.

注意:
根据docs，您设置的值因为 time_zone 不会改变，如果您将其设置为+01:00"；例如，time_zone 将被设置为 UTC 的偏移量，它不遵循 DST，因此它将全年保持不变.

只有命名的时区会在夏令时更改时间.

CET 之类的缩写将始终是冬季时间，CEST 将是夏季时间，而 +01:00 将始终是 UTC 时间 +1 小时，两者都不会随夏令时而改变.

system 时区将是安装 mysql 的主机的时区(除非 mysql 无法确定)

您可以在此处阅读更多有关使用 DST 的信息>

传奇人物 Jon Skeet 何时不使用 UTC:https://codeblog.jonskeet.uk/2019/03/27/storing-utc-is-not-a-silver-bullet/(例如未来的预定事件代表时间，而不是瞬间)

MySQL Timezone Cheatsheet

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 column is stored as UTC but 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

To select a timestamp column in UTC format

no matter what timezone the current MySQL session is in:

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:

SELECT `timestamp_field` FROM `table_name`

To select the current datetime in UTC:

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

Example result: 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

To get the current timezone

SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);

It will return 02:00:00 if your timezone is +2:00.

To get the current UNIX timestamp (in seconds):

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

To get the timestamp column as a UNIX timestamp

SELECT UNIX_TIMESTAMP(`timestamp`) FROM `table_name`

To get a UTC datetime column as a UNIX timestamp

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

SELECT FROM_UNIXTIME(`unix_timestamp_int`) FROM `table_name`

Get a UTC datetime from a UNIX timestamp

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

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

There are 3 places where the timezone might be set in MySQL:

Note: A timezone can be set in 2 formats:

an offset from UTC: '+00:00', '+10:00' or '-6:00'
as a named time zone: 'Europe/Helsinki', 'US/Eastern', or 'MET'

Named time zones can be used only if the time zone information tables in the mysql database have been created and populated.

in the file "my.cnf"

default_time_zone='+00:00'

timezone='UTC'

@@global.time_zone variable

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

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".

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

Note: you can not do this as it will return NULL:

SELECT 
CONVERT_TZ(`timestamp_field`, TIMEDIFF(NOW(), UTC_TIMESTAMP), '+00:00') AS `utc_datetime` 
FROM `table_name`

Setup mysql timezone tables

For CONVERT_TZ to work, you need the timezone tables to be populated

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

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 2011 https://chrisjean.com/updating-daylight-saving-time-on-linux/)

See the full DST (Daylight Saving Time) transition history for every timezone

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 also applies any necessary DST changes based on the rules in the above tables and the date that you use.

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.

Only the named timezones will change time during daylight savings time.

Abbreviations like CET will always be a winter time and CEST will be summer time while +01:00 will always be UTC time + 1 hour and both won't change with DST.

The system timezone will be the timezone of the host machine where mysql is installed (unless mysql fails to determine it)

You can read more about working with DST here

When not to use UTC by the legendary Jon Skeet: https://codeblog.jonskeet.uk/2019/03/27/storing-utc-is-not-a-silver-bullet/ (For example a scheduled event in the future that represents a time, not an instant in time)