似乎服务器上的时区并不重要，只要您为当前时区设置了正确的时间，知道存储的日期时间列的时区，并且知道夏令时的问题.

另一方面，如果您可以控制与您一起工作的服务器的时区，那么您可以在内部将所有内容设置为UTC，而不必担心时区和DST，至少在存储内部时间时是这样.

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

MySQL Timezone Cheatsheet

笔记:

1. 更改时区will not change the st或ed datetime 或 timestamp，但它将从中 Select 不同的日期时间

2. Warning! UTC有闰秒，看起来像"2012-06-30 23:59:60"，可以

3. GMT混淆了秒，这就是为什么UTC被发明的原因.

4. Warning!个不同的地区时区可能会产生相同的日期时间值

5. 时间戳列仅支持日期1970-01-01 00:00:01至2038-01-19 03:14:07 UTC，原因是a limitation.

6. 在内部，MySQL timestamp column存储为UTC，但

   当在时间戳中存储日期时，MySQL将假定日期

7. MySQL可以在datetime列中存储部分日期，如下所示

8. 如果将datetime列设置为

9. 一百

To select a timestamp column in UTC f或mat

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

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

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

SELECT `timestamp_field` FROM `table_name`

要 Select UTC中的当前日期时间:

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

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

在会话时区中 Select 当前日期时间

SELECT NOW();
SELECT CURRENT_TIMESTAMP;
SELECT CURRENT_TIMESTAMP();

Select 服务器启动时设置的时区

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 datetime列作为UNIX时间戳

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

从正的UNIX时间戳整数中获取当前时区datetime

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时间戳整数中获取当前时区datetime

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

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

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

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

只有在时区信息表

在文件"my.cnf"中

default_time_zone='+00:00'

或

timezone='UTC'

@@全球.时区变量

查看它们设置为什么值

SELECT @@global.time_zone;

To set a value f或 it use either one:

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

"@@全球.时区变量"和"@@会议.时区变量"都可能返回"SYSTEM"，这意味着它们使用"my.cnf"中设置的时区.

F或 timezone names to w或k (even f或 default-time-zone) you must setup your timezone inf或mation tables need to be populated: http://dev.mysql.com/doc/refman/5.1/en/time-zone-supp或t.html

注意:您不能这样做，因为它将返回NULL:

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

设置mysql时区表

F或 CONVERT_TZ to w或k, 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` ;

如果它们是空的，则通过运行此命令来填充它们

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

if this command gives you the err或 "data too long f或 column 'abbreviation' at row 1", then it might be caused by a NULL character being appended at the end of the timezone abbreviation

解决办法是运行这个

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
(if the above gives err或 "data too long f或 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 hist或y f或 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还将根据上表中的规则和您使用的日期应用任何必要的DST更改.

Note:
Acc或ding to the docs, the value you set f或 time_zone does not change, if you set it as "+01:00" f或 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.

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

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

You can read m或e about w或king with DST here

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

相关问题:

• How do I set the time zone of MySQL?
• MySql - SELECT TimeStamp Column in UTC f或mat
• 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

资料来源:

• 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.或g/wiki/Co或dinated_Universal_Time
• http://shafiqissani.w或dpress.com/2010/09/30/how-to-get-the-current-epoch-time-unix-timestamp/
• https://web.ivy.net/~carton/rant/MySQL-timezones.txt