似乎没有什么时区在服务器上,只要你有时间设置正确的当前时区,知道你存储的datetime列的时区,并知道夏令时的问题。
另一方面,如果你控制的服务器的时区的工作,那么你可以将一切设置为UTC内部,从不担心时区和DST。
这里有一些笔记,我收集了如何使用时区作为一种形式的骗子为我自己和其他人,可能影响什么时区的人将选择他/她的服务器,以及他/她将如何存储日期和时间。
MySQL时区Cheatsheet
笔记:
>更改时区不会更改存储的日期时间或
时间戳,但它会从中选择不同的日期时间
时间戳列
> UTC不使用夏令时,GMT(地区),GMT(时区)不(GMT也混淆了秒的定义,这是为什么发明UTC)。
>警告! UTC有闰秒,这些看起来像’2012-06-30 23:59:60’,可以
随机添加,提前6个月通知,由于减慢
地球旋转
>警告!不同的区域时区可能会产生相同的datetime值
到夏令时
>时间戳列仅支持1970-01-01 00:00:01至2038-01-19 03:14:07 UTC的日期
>内部一个MySQL timestamp column存储为UTC,但是
当选择一个日期时MySQL会自动将其转换为
当前会话时区。
当在时间戳中存储日期时,MySQL将假定日期
是在当前会话时区,并将其转换为UTC
存储。
> MySQL可以存储部分日期在datetime列,这些看起来像
“2013-00-00 04:00:00”
> MySQL store“0000-00-00 00:00:00”如果你设置datetime列为
NULL,除非你特别将列设置为允许null
创建它。
> 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时间戳记整数获取当前时区datetime
SELECT FROM_UNIXTIME(`unix_timestamp_int`) FROM `table_name`
从UNIX时间戳获取UTC datetime
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)
有3个地方可能在MySQL中设置时区:
注意:时区可以设置为2种格式:
>与UTC的偏移量:’00:00’,’10:00’或’-6:00′
>作为命名时区:“欧洲/赫尔辛基”,“美国/东部”或“MET”
Named time zones can be used only if the time zone information tables
in the mysql database have been created and populated.
在文件“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 = 'Europe/Helsinki';
SET @@global.time_zone='+00:00';
@@ session.time_zone变量
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”中设置的时区。
注意:你不能这样做,因为它将返回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
修复是运行这个
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
查看每个时区的完整夏令时(夏令时)转换历史记录
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。
系统时区将是安装mysql的主机的时区(除非mysql无法确定它)
您可以阅读有关使用DST here的更多信息
相关问题:
来源: