データベースのタイムゾーン付型について調べてみた
embulk-input-jdbcでこんな問題が上がっていたので、データベースのタイムゾーン付の型について調べてみた。
MySQL (5.6)
ドキュメントを見ると、TIMESTAMP型はタイムゾーンに対応しているらしい。
DB内ではUTC、クライアント側ではtime_zoneシステム変数で指定されたタイムゾーンになるようだ。
実際に試してみる。
mysql> show variables like '%time_zone%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | system_time_zone | UTC | | time_zone | UTC | +------------------+-------+ 2 rows in set (0.19 sec) mysql> create table test_tz ( -> id char(2), -> dt timestamp -> ); Query OK, 0 rows affected (0.59 sec) mysql> insert into test_tz values('01', '2016-03-13 14:00:00'); Query OK, 1 row affected (0.23 sec) mysql> select * from test_tz; +------+---------------------+ | id | dt | +------+---------------------+ | 01 | 2016-03-13 14:00:00 | +------+---------------------+ 1 row in set (0.16 sec)
time_zoneシステム変数がUTCなので、日時はUTCとして扱われる。
mysql> set time_zone = '+09:00'; Query OK, 0 rows affected (0.19 sec) mysql> show variables like '%time_zone%'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | UTC | | time_zone | +09:00 | +------------------+--------+ 2 rows in set (0.19 sec) mysql> select * from test_tz; +------+---------------------+ | id | dt | +------+---------------------+ | 01 | 2016-03-13 23:00:00 | +------+---------------------+ 1 row in set (0.17 sec)
time_zoneを変えると、日時はそのタイムゾーンで扱われる。
Oracle (12c)
Oracleでは、TIMESTAMP WITH TIME ZONE型とTIMESTAMP WITH LOCAL TIME ZONE型というのがある。
この辺りに詳しい説明があった。
まず、TIMESTAMP WITH TIME ZONE型を試してみる。
SQL> create table test_tz ( 2 id char(2), 3 dt timestamp with time zone 4 ); 表が作成されました。 SQL> insert into test_tz values('01', '2016-03-13 14:00:00'); insert into test_tz values('01', '2016-03-13 14:00:00') * 行1でエラーが発生しました。: ORA-01840: 入力した値の長さが日付書式に対して不足しています
明示的にタイムゾーンを指定しないといけないようだ。
SQL> insert into test_tz values('01', '2016-03-13 14:00:00+09:00'); 1行が作成されました。 SQL> select * from test_tz; ID DT ---- --------------------------------------------------------------------------- 01 16-03-13 14:00:00.000000 +09:00
SQL> select * from test_tz where dt='2016-03-13 05:00:00+00:00'; ID DT ---- --------------------------------------------------------------------------- 01 16-03-13 14:00:00.000000 +09:00
次に、TIMESTAMP WITH LOCAL TIME ZONE型を試してみる。
SQL> create table test_ltz ( 2 id char(2), 3 dt timestamp with local time zone 4 ); 表が作成されました。 SQL> insert into test_ltz values('01', '2016-03-13 14:00:00'); 1行が作成されました。 SQL> select * from test_ltz; ID DT ---- --------------------------------------------------------------------------- 01 16-03-13 14:00:00.000000
こちらではタイムゾーンの指定は必要ない。
selectすると、insertした通りの日時で表示される。
これを、UTC環境のOracleクライアントから参照してみる。
SQL> select * from test_ltz; ID DT -- --------------------------------------------------------------------------- 01 13-MAR-16 05.00.00.000000 AM
そうすると、UTCに変換されて表示された。
つまり、クライアント環境のタイムゾーンで扱われるということだ。
SQL Server (2012)
ドキュメントによると、datetimeoffset型がタイムゾーンを保持できる。
2> create table test_tz ( 3> id char(2), 4> dt datetimeoffset 5> ); 6> go 1> 2> select * from test_tz 3> go id dt ---- ---------------------------------- (0 行処理されました) 1> insert into test_tz values('01', '2016-03-13 14:00:00') 2> go (1 行処理されました) 1> select * from test_tz 2> go id dt ---- ---------------------------------- 01 2016-03-13 14:00:00.0000000 +00:00 (1 行処理されました)
JST環境のSQL Serverだが、デフォルトではUTCになるようだ。
タイムゾーンを明示的に指定することもできる。
1> insert into test_tz values('01', '2016-03-13 14:00:00+09:00') 2> go (1 行処理されました) 1> select * from test_tz 2> go id dt ---- ---------------------------------- 01 2016-03-13 14:00:00.0000000 +00:00 01 2016-03-13 14:00:00.0000000 +09:00 (2 行処理されました)
PostgreSQL (9.4)
ドキュメントによると、DB内ではUTC、クライアント側ではTimeZoneシステムパラメータで指定されたタイムゾーンになるようだ。
実際に試してみる。
postgres=> show timezone; TimeZone ---------- UTC (1 行) postgres=> create table test_tz ( postgres(> id char(2), postgres(> dt timestamp with time zone postgres(> ); CREATE TABLE ^ postgres=> insert into test_tz values('01', '2016-03-13 14:00:00'); INSERT 0 1 postgres=> select * from test_tz; id | dt ----+------------------------ 01 | 2016-03-13 14:00:00+00 (1 行)
たしかに、TimeZoneの値であるUTCで解釈されている。
タイムゾーンを明示的に指定してinsertすることもできる。
postgres=> insert into test_tz values('01', '2016-03-13 14:00:00+09:00'); INSERT 0 1 postgres=> select * from test_tz; id | dt ----+------------------------ 01 | 2016-03-13 14:00:00+00 01 | 2016-03-13 05:00:00+00 (2 行)
TimeZoneパラメータを変えると、そのタイムゾーンで解釈されるようになる。
postgres=> show timezone; TimeZone ---------- Japan (1 行) postgres=> select * from test_tz; id | dt ----+------------------------ 01 | 2016-03-13 23:00:00+09 01 | 2016-03-13 14:00:00+09 (2 行) postgres=> insert into test_tz values('03', '2016-03-13 15:00:00'); INSERT 0 1 postgres=> select * from test_tz; id | dt ----+------------------------ 01 | 2016-03-13 23:00:00+09 01 | 2016-03-13 14:00:00+09 03 | 2016-03-13 15:00:00+09 (3 行)
Redshift
ドキュメントによると、タイムゾーン付のTIMESTAMP型はまだサポートされていないようだ。
まとめ
各DBMSにおいて、INSERTする日時とかSELECTされた日時がどのタイムゾーンになるかをまとめてみた。
DBMS | 型 | タイムゾーン |
---|---|---|
MySQL | TIMESTAMP | time_zoneシステム変数による |
Oracle | TIMESTAMP WITH TIME ZONE | 明示する |
TIMESTAMP WITH LOCAL TIME ZONE | クライアント環境のタイムゾーン | |
SQL Server | DATETIMEOFFSET | UTC |
PostgreSQL | time/timestamp with time zone | TimeZoneシステムパラメータによる |
Redshift | - | - |