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（世界協定時）で記録されており、クライアントのタイムゾーン設定に合わせて返す文字列が変化する。
タイムゾーンを設定する
クライアントのタイムゾーンを 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 サーバのタイムゾーンでの時刻になる。
