確認したいこと
PostgreSQLのTIMESTAMP WITH TIME ZONE型の列に日付データを入れるとき、クライアントとサーバーのタイムゾーンが異なる(サーバーが外国にあるなど)ときの動作を確認する。
確認のためのテーブルを生成
CREATE TABLE table1
(
id INTEGER
, date_tz TIMESTAMP WITH TIME ZONE
, description TEXT
);
クライアントのタイムゾーンがAsia/Tokyoで、サーバーのタイムゾーンもAsia/Tokyo
次のSQLでテーブルにデータを入れる。
INSERT INTO table1 (id, date_tz, description) VALUES (
1
, TO_DATE('2016-01-01','YYYY-MM-DD')
, 'timezone=Asia/Tokyo, 2016-01-01'
);
データを確認する。
SELECT id, date_tz, description FROM table1 ORDER BY id;
id | date_tz | description
----+------------------------+---------------------------------
1 | 2016-01-01 00:00:00+09 | timezone=Asia/Tokyo, 2016-01-01
(1 行)
クライアントのタイムゾーンがAsia/Tokyoで、サーバーのタイムゾーンがAsia/Taipei
postgresql.confのtimezoneをAsia/Taipeiに変更して、再起動する。
今入っているデータを確認する。
id | date_tz | description
----+------------------------+---------------------------------
1 | 2015-12-31 23:00:00+08 | timezone=Asia/Tokyo, 2016-01-01
(1 行)
日本時間の2016年1月1日0時は、台北では2015年12月31日23時なので、これであっている。
クライアントが日本にあっても、SQLの結果はサーバーのタイムゾーンで返ってくるようだ。
次のSQLでテーブルにデータを入れる。
INSERT INTO table1 (id, date_tz, description) VALUES (
2
, TO_DATE('2016-01-01','YYYY-MM-DD')
, 'timezone=Asia/Taipei, 2016-01-01'
);
データを確認する。
SELECT id, date_tz, description FROM table1 ORDER BY id;
id | date_tz | description
----+------------------------+---------------------------------
1 | 2015-12-31 23:00:00+08 | timezone=Asia/Tokyo, 2016-01-01
2 | 2016-01-01 00:00:00+08 | timezone=Asia/Taipei, 2016-01-01
(2 行)
id=2のdate_tzは、2016-01-01 00:00:00+08となり、台北時間の2016年1月1日0時となる。
INSERT文を実行したクライアントが日本にあっても、SQLの結果はサーバーのタイムゾーンに従うようだ。
そもそも、TO_DATE関数の戻り値はDATE型で、これをTIMESTAMP WITH TIME ZONE型の列に入れようとしているのだから、DATE型からTIMESTAMP WITH TIME ZONE型にCASTされるということなので
SELECT TO_DATE('2016-01-01','YYYY-MM-DD')::TIMESTAMP WITH TIME ZONE;
to_date
------------------------
2016-01-01 00:00:00+08
(1 行)
この結果が書き込まれるということかな。
クライアントのタイムゾーンがAsia/Tokyoで、サーバーのタイムゾーンもAsia/Tokyo
postgresql.confのtimezoneをAsia/Tokyoに戻して、再起動する。
今入っているデータを確認する。
SELECT id, date_tz, description FROM table1 ORDER BY id;
id | date_tz | description
----+------------------------+---------------------------------
1 | 2016-01-01 00:00:00+09 | timezone=Asia/Tokyo, 2016-01-01
2 | 2016-01-01 01:00:00+09 | timezone=Asia/Taipei, 2016-01-01
(2 行)
id=2のdate_tzは、台北時間の2016年1月1日0時だったので、日本時間では2016年1月1日時1時となる。
結論
TIMESTAMP WITH TIME ZONE型のデータは、クライアントがどこの国であろうが、サーバーのタイムゾーンに従う。