PostgreSQL の datetime with time zone 型を使う

PostgreSQL の time 型や timestamp 型には、タイムゾーン情報を含まない without time zone と、タイムゾーンを扱える with time zone の 2 種類がある。これは SQL99 に準拠した仕様である。

テーブルを作成する

テーブルを作成するときに型の指定に with time zone を加えるだけで使えるようになる。動作を比較するため、with time zone を付けないカラムも用意する。

CREATE TABLE tt (
	id serial PRIMARY KEY
,	d timestamp
,	dz timestamp with time zone
);

データを追加

作成したテーブルにデータを追加する。

INSERT INTO tt (d, dz) VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO tt (d, dz) VALUES ('2015-04-07 12:00:00', '2015-04-07 12:00:00');
INSERT INTO tt (d, dz) VALUES ('2015-04-07 12:00:00-04', '2015-04-07 12:00:00-04');

SELECT * FROM tt;
id d dz
1 2015-04-07 11:04:21.596 2015-04-07 11:04:21.596+09
2 2015-04-07 12:00:00 2015-04-07 12:00:00+09
3 2015-04-07 12:00:00 2015-04-08 01:00:00+09

Without time zone のほうは、タイムゾーンの指定(-04)が無視されている。
With time zone のデータは UTC(世界協定時)で記録されており、クライアントのタイムゾーン設定に合わせて返す文字列が変化する。

タイムゾーンを確認する

次のコマンドでクライアントのタイムゾーンを確認することができる。

SHOW TIME ZONE;
TimeZone Asia/Tokyo

タイムゾーンを設定する

クライアントのタイムゾーンを America/New_York に設定し、先ほどと同じようにデータを追加してみる。

SET TIME ZONE 'America/New_York';
INSERT INTO tt (d, dz) VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO tt (d, dz) VALUES ('2015-04-07 12:00:00', '2015-04-07 12:00:00');
INSERT INTO tt (d, dz) VALUES ('2015-04-07 12:00:00-04', '2015-04-07 12:00:00-04');
id d dz
1 2015-04-07 11:04:21.596 2015-04-06 22:04:21.596-04
2 2015-04-07 12:00:00 2015-04-06 23:00:00-04
3 2015-04-07 12:00:00 2015-04-07 12:00:00-04
4 2015-04-06 22:09:11.805 2015-04-06 22:09:11.805-04
5 2015-04-07 12:00:00 2015-04-07 12:00:00-04
6 2015-04-07 12:00:00 2015-04-07 12:00:00-04

With time zone のカラムはタイムゾーンを指定せずにデータを追加すると、現在設定されているタイムゾーンの時刻として扱うようになっている (id = 2, 5)。タイムゾーンを指定したデータは同じ時刻を指している (id = 3, 6)。
Without time zone では、同じ CURRENT_STAMP を指定しても、クライアントのタイムゾーンの設定によってばらばらの時刻になってしまう。ここで記録される時刻は PostgreSQL サーバのタイムゾーンでの時刻になる。