フォローアップの質問https://serverfault.com/questions/191331/should-servers-have-their-timezone-set-to-gmt-utc
MySQL のタイムゾーンは UTC に設定する必要がありますか、それともサーバーまたは PHP と同じタイムゾーンに設定する必要がありますか? (UTC でない場合)
長所と短所は何ですか?
ベストアンサー1
現在のタイムゾーンに正しく時刻が設定され、保存する日時列のタイムゾーンがわかっていて、夏時間の問題を認識している限り、サーバーのタイムゾーンが何であるかは問題ではないようです。
一方、作業するサーバーのタイムゾーンを制御できる場合は、すべてを内部的に UTC に設定でき、少なくとも内部時間の保存に関しては、タイムゾーンや DST について心配する必要はありません。
ここでは、タイムゾーンの操作方法について私が収集したメモをいくつか紹介します。これは、サーバーにどのタイムゾーンを選択するか、日付と時刻をどのように保存するかに影響する可能性がある、私自身と他の人のためのチートシートの形式です。
MySQL タイムゾーン チートシート
ノート:
タイムゾーンの変更保存された日付時刻やタイムスタンプは変更されませんただし、タイムスタンプ列から異なる日時が選択されます
警告!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 は日付が現在のセッションのタイムゾーンにあると想定し、保存のために UTC に変換します。
MySQL は日付列に部分的な日付を保存できます。これは「2013-00-00 04:00:00」のようになります。
MySQL では、datetime 列を NULL に設定すると、列の作成時に NULL を許可するように明示的に設定しない限り、「0000-00-00 00:00:00」が保存されます。
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タイムスタンプ整数から現在のタイムゾーンの日付時刻を取得します。
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'
名前付きタイムゾーンは、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 = '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
このコマンドでエラーが発生した場合は、「行 1 の列「略語」のデータが長すぎます"の場合、タイムゾーンの略語の末尾に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
また、上記の表のルールと使用する日付に基づいて、必要な DST の変更も適用されます。
注記:
によるドキュメントtime_zone に設定した値は変更されません。たとえば、「+01:00」に設定すると、time_zone は UTC からのオフセットとして設定され、DST に従わないため、年間を通じて同じ値になります。
名前が付けられたもののみ時間帯夏時間中は時間が変更されます。
のような略語はCET
常に冬時間、CEST
は夏時間ですが、+01:00 は常にUTC
時刻 + 1 時間であり、どちらも DST によって変更されません。
タイムsystem
ゾーンは、mysql がインストールされているホスト マシンのタイムゾーンになります (mysql がタイムゾーンを判別できない場合を除く)
DSTの使用に関する詳細は以下をご覧ください。ここ
伝説の Jon Skeet による UTC を使用しない場合:https://codeblog.jonskeet.uk/2019/03/27/storing-utc-is-not-a-silver-bullet/(たとえば、時間を表す将来の予定イベントであり、瞬間的なものではありません)
関連する質問:
- MySQL のタイムゾーンを設定するにはどうすればよいですか?
- MySql - UTC 形式でタイムスタンプ列を選択する
- MySQL で UTC 時間から Unix タイムスタンプを取得するにはどうすればいいですか?
- サーバーの MySQL タイムスタンプを UTC に変換する
- https://dba.stackexchange.com/questions/20217/mysql-set-utc-time-as-default-timestamp
- MySQL の現在のタイムゾーンを取得するにはどうすればよいですか?
- MySQL の日付時刻フィールドと夏時間 - 「追加の」時間を参照するにはどうすればよいでしょうか?
- FROM_UNIXTIMEからの負の値を変換する
出典:
- 参考:
- http://dev.mysql.com/doc/refman/5.0/en/日付と時刻関数.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