CentOS6.4とMySQL5.6を利用したアプリケーション上で、複数のタイムゾーンに対応する必要があったので、調査したメモです。
MySQLのタイムゾーン設定は、グローバルとローカルの2種類があります。
グローバルはデフォルトとなり、全てのセッションでこの値がデフォルトとして使われます。
ローカルは、各セッションでの設定です。
セッション開始時に、グローバルの値がローカルのデフォルト値として設定されます。
設定が異なる場合に、セッション単位で変更することができます。
そして、MySQLのタイムゾーンは、通常のインストールではOSのタイムゾーンに準じます。
このため、日本のタイムゾーンを指定してOSがセットアップされていれば、"Asia/Tokyo"となっていると思います。
MySQLのタイムゾーンは、以下のようにして調べられます。
"SYSTEM"となっています。要は「OSと同じだよ」という意味ですね。
現在時刻は、日本時間10時となっています。
日本だけで使っているシステムであればこれで良いのですが、今回は標準時をUTCにしたいと思いますので、変更します。
もちろん、ここは"SYSTEM"のままで、OSのタイムゾーンをUTCにしても良いです。
MySQLのタイムゾーンを変更する場合、まずはMySQLで使用するタイムゾーンをテーブルに取り込む必要があります。
シェル上から、以下のようにして実行します。
いくつか警告が出ますが、タイムゾーンファイル側のバグのようなので、関連タイムゾーンで影響が無ければこのままでOKです。
現在時刻は、日本時間10時のままです。
これは、既に始まったセッション上では、まだ以前のデフォルト設定でローカル値のままだからです。
こちらも変更してみます。今度はglobalの指定をしないでsetします。
今度は、例えば、アメリカ東部時間にしてみます。
mysql> SET @@session.time_zone='America/New_York';
Query OK, 0 rows affected (0.00 sec)
アメリカ東部時間、昨日の20時に変わりました。
今現在、以下の3つのタイムゾーンが存在していることになります。
・OSのタイムゾーン[Asia/Tokyo] ・データベースのタイムゾーン[UTC] ・このセッションのタイムゾーン[America/New_York]
今は、このセッションがアメリカ東部時間になっただけです。
一度このセッションを終わらせて、別のセッションを開始してみます。
今度は、UTC時間、1時になりました。これがセッション開始時のデフォルトのタイムゾーンです。
このように、ローカルタイムゾーンをセッション開始時に変更することで、データベース上でも時刻表示が変わることが分かりました。
例えばPHPのデータベースアクセスの最初でローカルタイムゾーンを変更すれば、うまく行けそうですね。
次に、時間フィールドを含むテーブルのレコード追加、検索について調べてみます。
AUTO_INCREMENTのid列と、DATETIME型、TIMESTAMP型の列で構成される簡単なテーブルを定義します。
再度このセッションのタイムゾーンを"America/New_York"に変更して、一つ、レコードを入れてみます。
現在時刻を、DATETIME型とTIMESTAMP型に入れました。
検索して確認してみます。
どちらもアメリカ東部時間、昨日の20時で時間を確認できました。
それでは、また一度セッションを終わらせて、別のセッションを開始してみます。
初期状態のセッションはUTCになっています。先ほどの検索結果はどのように表示されますでしょうか。
DATETIME型ではアメリカ東部時間の値のまま、TIMESTAMP型では、アメリカ東部時間がUTCに変換された内容となっています。
今はUTCで表示していますので、アメリカ東部時間のセッションで昨日の20時で追加したレコードは、1時で表示されて欲しいところです。
http://dev.mysql.com/doc/refman/5.6/en/datetime.htmlによると、この2つの型は以下の違いがあります。
・DATETIME型は、DMLに指定した値をそのまま格納する
・TIMESTAMP型は、DMLに指定した値をいったんUTCに変換して格納する
ユーザーによってタイムゾーンを変えて表示・登録しなければならないケースでは、TIMESTAMP型を使うのが良いですね。
ただし、TIMESTAMP型は4バイトのため、2038年問題にはぶちあたってしまいますのでご注意を・・・。
ちなみに、MAXDB SQLモードでは、TIMESTAMPはDATETIMEとして動作するとのことです。
余談ですが、今のMySQLは、昔のMySQLのようにTIMESTAMP型を指定してもデフォルトで日時を入れてくれたりしません。
NULLが入ります。
MySQLのタイムゾーン設定は、グローバルとローカルの2種類があります。
グローバルはデフォルトとなり、全てのセッションでこの値がデフォルトとして使われます。
ローカルは、各セッションでの設定です。
セッション開始時に、グローバルの値がローカルのデフォルト値として設定されます。
設定が異なる場合に、セッション単位で変更することができます。
そして、MySQLのタイムゾーンは、通常のインストールではOSのタイムゾーンに準じます。
このため、日本のタイムゾーンを指定してOSがセットアップされていれば、"Asia/Tokyo"となっていると思います。
MySQLのタイムゾーンは、以下のようにして調べられます。
mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | SYSTEM |
+--------------------+---------------------+
1 row in set (0.01 sec)
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2014-02-25 10:00:00 |
+---------------------+
1 row in set (0.00 sec)
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | SYSTEM |
+--------------------+---------------------+
1 row in set (0.01 sec)
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2014-02-25 10:00:00 |
+---------------------+
1 row in set (0.00 sec)
"SYSTEM"となっています。要は「OSと同じだよ」という意味ですね。
現在時刻は、日本時間10時となっています。
日本だけで使っているシステムであればこれで良いのですが、今回は標準時をUTCにしたいと思いますので、変更します。
もちろん、ここは"SYSTEM"のままで、OSのタイムゾーンをUTCにしても良いです。
MySQLのタイムゾーンを変更する場合、まずはMySQLで使用するタイムゾーンをテーブルに取り込む必要があります。
シェル上から、以下のようにして実行します。
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh87' as time zone. Skipping it.
～中略～
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh87' as time zone. Skipping it.
～中略～
いくつか警告が出ますが、タイムゾーンファイル側のバグのようなので、関連タイムゾーンで影響が無ければこのままでOKです。
mysql> SET @@global.time_zone='UTC';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| UTC | SYSTEM |
+--------------------+---------------------+
1 row in set (0.00 sec)
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2014-02-25 10:00:10 |
+---------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| UTC | SYSTEM |
+--------------------+---------------------+
1 row in set (0.00 sec)
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2014-02-25 10:00:10 |
+---------------------+
1 row in set (0.00 sec)
現在時刻は、日本時間10時のままです。
これは、既に始まったセッション上では、まだ以前のデフォルト設定でローカル値のままだからです。
こちらも変更してみます。今度はglobalの指定をしないでsetします。
今度は、例えば、アメリカ東部時間にしてみます。
mysql> SET @@session.time_zone='America/New_York';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2014-02-24 20:00:20 |
+---------------------+
1 row in set (0.00 sec)
+---------------------+
| NOW() |
+---------------------+
| 2014-02-24 20:00:20 |
+---------------------+
1 row in set (0.00 sec)
アメリカ東部時間、昨日の20時に変わりました。
今現在、以下の3つのタイムゾーンが存在していることになります。
・OSのタイムゾーン[Asia/Tokyo] ・データベースのタイムゾーン[UTC] ・このセッションのタイムゾーン[America/New_York]
今は、このセッションがアメリカ東部時間になっただけです。
一度このセッションを終わらせて、別のセッションを開始してみます。
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2014-02-25 01:00:30 |
+---------------------+
1 row in set (0.00 sec)
+---------------------+
| NOW() |
+---------------------+
| 2014-02-25 01:00:30 |
+---------------------+
1 row in set (0.00 sec)
今度は、UTC時間、1時になりました。これがセッション開始時のデフォルトのタイムゾーンです。
このように、ローカルタイムゾーンをセッション開始時に変更することで、データベース上でも時刻表示が変わることが分かりました。
例えばPHPのデータベースアクセスの最初でローカルタイムゾーンを変更すれば、うまく行けそうですね。
次に、時間フィールドを含むテーブルのレコード追加、検索について調べてみます。
AUTO_INCREMENTのid列と、DATETIME型、TIMESTAMP型の列で構成される簡単なテーブルを定義します。
mysql> CREATE TABLE TIME_TEST (id INTEGER AUTO_INCREMENT PRIMARY KEY,dt DATETIME,ts TIMESTAMP);
Query OK, 0 rows affected (0.04 sec)
再度このセッションのタイムゾーンを"America/New_York"に変更して、一つ、レコードを入れてみます。
mysql> SET @@session.time_zone='America/New_York';
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO TIME_TEST (dt,ts) VALUES (NOW(),NOW());
Query OK, 1 row affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO TIME_TEST (dt,ts) VALUES (NOW(),NOW());
Query OK, 1 row affected (0.01 sec)
現在時刻を、DATETIME型とTIMESTAMP型に入れました。
検索して確認してみます。
mysql> SELECT * FROM TIME_TEST;
+----+---------------------+---------------------+
| id | dt | ts |
+----+---------------------+---------------------+
| 1 | 2014-02-24 20:00:40 | 2014-02-24 20:00:40 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
+----+---------------------+---------------------+
| id | dt | ts |
+----+---------------------+---------------------+
| 1 | 2014-02-24 20:00:40 | 2014-02-24 20:00:40 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
どちらもアメリカ東部時間、昨日の20時で時間を確認できました。
それでは、また一度セッションを終わらせて、別のセッションを開始してみます。
初期状態のセッションはUTCになっています。先ほどの検索結果はどのように表示されますでしょうか。
mysql> SELECT * FROM TIME_TEST;
+----+---------------------+---------------------+
| id | dt | ts |
+----+---------------------+---------------------+
| 1 | 2014-02-24 20:00:50 | 2014-02-25 01:00:50 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
+----+---------------------+---------------------+
| id | dt | ts |
+----+---------------------+---------------------+
| 1 | 2014-02-24 20:00:50 | 2014-02-25 01:00:50 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
DATETIME型ではアメリカ東部時間の値のまま、TIMESTAMP型では、アメリカ東部時間がUTCに変換された内容となっています。
今はUTCで表示していますので、アメリカ東部時間のセッションで昨日の20時で追加したレコードは、1時で表示されて欲しいところです。
http://dev.mysql.com/doc/refman/5.6/en/datetime.htmlによると、この2つの型は以下の違いがあります。
・DATETIME型は、DMLに指定した値をそのまま格納する
・TIMESTAMP型は、DMLに指定した値をいったんUTCに変換して格納する
ユーザーによってタイムゾーンを変えて表示・登録しなければならないケースでは、TIMESTAMP型を使うのが良いですね。
ただし、TIMESTAMP型は4バイトのため、2038年問題にはぶちあたってしまいますのでご注意を・・・。
ちなみに、MAXDB SQLモードでは、TIMESTAMPはDATETIMEとして動作するとのことです。
余談ですが、今のMySQLは、昔のMySQLのようにTIMESTAMP型を指定してもデフォルトで日時を入れてくれたりしません。
mysql> INSERT INTO TIME_TEST () VALUE();
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM TIME_TEST;
+----+---------------------+---------------------+
| id | dt | ts |
+----+---------------------+---------------------+
| 1 | 2014-02-24 20:26:46 | 2014-02-25 01:26:46 |
| 2 | NULL | NULL |
+----+---------------------+---------------------+
2 rows in set (0.00 sec)
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM TIME_TEST;
+----+---------------------+---------------------+
| id | dt | ts |
+----+---------------------+---------------------+
| 1 | 2014-02-24 20:26:46 | 2014-02-25 01:26:46 |
| 2 | NULL | NULL |
+----+---------------------+---------------------+
2 rows in set (0.00 sec)
NULLが入ります。