[解決済み] MySQL のタイムゾーンは UTC に設定する必要がありますか？

2022-04-23 03:01:14

質問

のフォローアップの質問 https://serverfault.com/questions/191331/should-servers-have-their-timezone-set-to-gmt-utc

MySQLのタイムゾーンはUTCに設定すべきでしょうか、それともサーバーやPHPが設定しているタイムゾーンと同じに設定すべきでしょうか？(UTCでない場合)

長所と短所は何ですか？

解決方法は？

現在のタイムゾーンの時刻を正しく設定し、保存するdatetimeカラムのタイムゾーンを把握し、サマータイムの問題を認識していれば、サーバーのタイムゾーンは関係ないようです。

一方、使用するサーバーのタイムゾーンを管理できるのであれば、内部ですべてをUTCに設定し、少なくとも内部時刻の保存に関しては、タイムゾーンやサマータイムを気にする必要はないでしょう。

ここでは、タイムゾーンをどのように扱うかについて、私自身や他の人のためのカンニングペーパーとして集めたメモを紹介します。このメモは、その人が自分のサーバーにどのタイムゾーンを選び、どのように日付と時刻を保存するかに影響するかもしれません。

MySQLタイムゾーンチートシート

注意事項

タイムゾーンを変更する は、保存されている日時やタイムスタンプ からは異なる日時が選択されます。 timestampカラム
警告! UTCにはうるう秒があり、これは「2012-06-30 23:59:60」のように表示されます。 6ヶ月前に予告して、ランダムに追加されます。地球の自転
GMTは秒を混乱させるので、UTCが発明されたのです。
警告! 異なる地域のタイムゾーンが同じ日付時刻の値を生成する場合があります。夏時間のため
タイムスタンプ列は、1970-01-01 00:00:01 ～ 2038-01-19 03:14:07 UTC の日付のみをサポートしています。制限事項 .
内部的には MySQLのタイムスタンプ列として保存されます。 UTC しかしを選択すると、MySQL は自動的に日付を変換します。現在のセッションのタイムゾーン。

タイムスタンプに日付を格納する場合、MySQL は日付が「0」であると仮定します。は現在のセッションタイムゾーンであるため、UTCに変換して使用します。を保存します。
MySQLは日付の一部をdatetimeカラムに格納することができ、以下のようになります。 "2013-00-00 04:00:00"
MySQLは、datetimeカラムに "0000-00-00 00:00:00 "を設定すると、"0000-00-00 00:00"を格納します。ただし、NULLを許容するよう特別に設定した場合はこの限りではありません。を作成します。
これを読む

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();

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 タイムスタンプの正の整数値から現在のタイムゾーンの時刻を取得します。

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 でタイムゾーンが設定される可能性がある場所は 3 つあります。

注：タイムゾーンは2つのフォーマットで設定できます。

UTCからのオフセット。00:00'、'+10:00'、'-6:00' のいずれかを指定します。
名前付きタイムゾーン： 'Europe/Helsinki', 'US/Eastern', または 'MET'.

名前付きタイムゾーンは、タイムゾーン情報テーブルがある場合にのみ使用できます。が作成され、入力されています。

をファイル "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 variable" と "@@session.time_zone variable" の両方が "SYSTEM" を返す可能性がありますが、これは "my.cnf" で設定したタイムゾーンを使用するということを意味します。

タイムゾーン名が機能するためには（default-time-zoneであっても）、タイムゾーン情報テーブルが入力されている必要があります。 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

このコマンドでエラーが発生した場合 " 1 行目の列 'abbreviation' に対してデータが長すぎる。この場合、タイムゾーンの略語の末尾に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

(サーバーのdstルールが最新であることを確認します。 zdump -v Europe/Moscow | grep 2011 https://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 また、上記の表のルールと使用する日付に基づいて、必要な夏時間の変更も適用されます。

注意

によるとドキュメント例えば、"+01:00"と設定すると、time_zoneはUTCからのオフセットとして設定され、夏時間に従わないため、一年中同じ値になります。

という名前のものだけです。タイムゾーンは夏時間の間、時刻を変更します。

などの略語は CET は必ず冬時間になり CEST は夏時間となり、+01:00 は常に UTC 時間＋1時間で、どちらも夏時間で変わることはありません。

その system タイムゾーンは、mysql がインストールされているホストマシンのタイムゾーンになります (mysql が判断に失敗しない限り)。

サマータイムを使った作業については、こちらをご覧ください。ここで

伝説のジョン・スキートによる、UTCを使用してはいけない場合。 https://codeblog.jonskeet.uk/2019/03/27/storing-utc-is-not-a-silver-bullet/ (例えば、未来に予定されている出来事で、時刻を表すもの。）