今日もプログラミング

IT技術とかプログラミングのこととか特にJavaを中心に書いていきます

データベースのタイムゾーン付型について調べてみた

Embulk Oracle SQL Server

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

JSTで格納されているが、UTCで検索することもできる。

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 行)

JSTからUTCに変換されて格納されているようだ。

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 - -