本文介紹了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 的時區?
MySql - UTC 格式的 SELECT 時間戳列
如何從 UTC 獲取 MySQL 中的 Unix 時間戳時間?
將服務器 MySQL 時間戳轉換為 UTC
https://dba.stackexchange.com/questions/20217/mysql-set-utc-time-as-default-timestamp
如何獲取當前時區MySQL?
MySQL 日期時間字段和夏令時——我如何引用額外"時間?小時?
從 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.org/wiki/Coordinated_Universal_Time
http://shafiqissani.wordpress.com/2010/09/30/how-to-get-the-current-epoch-time-unix-timestamp/
https://web.ivy.net/~carton/rant/MySQL-timezones.txt

Follow up question of https://serverfault.com/questions/191331/should-servers-have-their-timezone-set-to-gmt-utc

Should the MySQL timezone be set to UTC or should it be set to be the same timezone as the server or PHP is set? (If it is not UTC)

What are the pros and cons?

解決方案

It seems that it does not matter what timezone is on the server as long as you have the time set right for the current timezone, know the timezone of the datetime columns that you store, and are aware of the issues with daylight savings time.

On the other hand if you have control of the timezones of the servers you work with then you can have everything set to UTC internally and never worry about timezones and DST.

Here are some notes I collected of how to work with timezones as a form of cheatsheet for myself and others which might influence what timezone the person will choose for his/her server and how he/she will store date and time.

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)

久久久久久久av_日韩在线中文_看一级毛片视频_日本精品二区_成人深夜福利视频_武道仙尊动漫在线观看

MySQL 是否應該將其時區設置為 UTC?