とあるプロジェクトで PostgreSQL を利用していて、日時を主キーの一部に利用しているテーブルがありました。
そのカラム型が TIMESTAMP WITH TIME ZONE だったので、ふと「あれ？これってタイムゾーンが違う場合は違うレコードになるのかな？」と思って試してみたのでメモしておきます。
問題のテーブル
問題となったテーブルは以下のような感じです。本当は他にもっとカラムがたくさんあるんですが、今回は必要ないので割愛しています。
よくある受発注を扱うようなシステムで、「【注文】はある【商品】を【いつ注文したか】で一意に識別できる」という前提のもとでproduct_idとordered_atを主キーにしていました。
postgres=# CREATE TABLE orders ( product_id integer, ordered_at timestamp with time zone, CONSTRAINT pkey PRIMARY KEY (product_id, ordered_at) ) ;
CREATE TABLE
postgres=# \d orders
Table "public.orders"
Column | Type | Modifiers
------------+--------------------------+-----------
product_id | integer | not null
ordered_at | timestamp with time zone | not null
Indexes:
"pkey" PRIMARY KEY, btree (product_id, ordered_at)
さて、ご覧の通り、ordered_atのデータ型はtimestamp with time zoneとなっており、時刻だけでなくタイムゾーン情報も含みます。この場合、同じ時刻だけどタイムゾーンが違う場合には主キー制約違反になるのだろうかというのが冒頭の問題です。
そもそも timestamp with time zone って？
その名の通りですが、時刻にタイムゾーン情報も含めて保持するデータ型です。
timestamp without time zone という型もあり、PostgreSQL では別の型として扱われます。
PostgreSQL の Date/Time 関係のデータ型には以下のページを参照してください。
http://www.postgresql.jp/document/9.3/html/datatype-datetime.html
早速試してみた
まずは商品 ID が 1 の商品に対して注文レコードを作ってみます。
postgres=# INSERT INTO orders (product_id, ordered_at) VALUES (1, '2014-07-01 14:00:00') ;
INSERT 0 1
僕の手元の Mac ではタイムゾーンが JST になっているので特にタイムゾーンを明示していない上記のクエリは
JST 扱いでレコード登録されているようです。
postgres=# SELECT product_id, ordered_at FROM orders ;
product_id | ordered_at
------------+------------------------
1 | 2014-07-01 14:00:00+09
(1 row)
次に UTC を指定して注文をしてみます。
postgres=# INSERT INTO orders (product_id, ordered_at) VALUES (1, '2014-07-01 14:00:00 UTC') ;
INSERT 0 1
postgres=# SELECT product_id, ordered_at FROM orders ;
product_id | ordered_at
------------+------------------------
1 | 2014-07-01 14:00:00+09
1 | 2014-07-01 23:00:00+09
(2 rows)
JST に変換されて登録されました。
あれ、ということは…？
postgres=# INSERT INTO orders (product_id, ordered_at) VALUES (1, '2014-07-01 05:00:00 UTC') ;
ERROR: duplicate key value violates unique constraint "pkey"
DETAIL: Key (product_id, ordered_at)=(1, 2014-07-01 14:00:00+09) already exists.
同時刻のレコードを登録しようとしたら主キー制約違反となりました。
PostgreSQL のドキュメントを参照
timestamp with time zoneについて内部に格納されている値は常にUTCです（協定世界時、歴史的にグリニッジ標準時GMTとして知られています）。 時間帯が明示的に指定された入力値は、その時間帯に適したオフセットを使用してUTCに変換されます。 入力文字列に時間帯が指定されていない場合は、システムのTimeZoneパラメータに示されている値が時間帯とみなされ、timezone時間帯用のオフセットを使用してUTCに変換されます。
とのことで、内部的には UTC で保持しているらしいですね。
逆に言えば入力時のタイムゾーンはデータとして残らないということになります。
時刻は時刻であってそれ以上の情報は別途データとして記録できるように設計しないとダメということですね。例えば、その注文をおこなったのが日本支社のオペレータかイギリス支社のオペレータか等をタイムゾーンから判断することもできるかと思っていましたが、そんなことはないようです。
最後に
そもそも時刻を主キーにしてんじゃねーよ！というツッコミはここでは受け付けません。(笑)
自然キーが見つからず、かと言ってサロゲートキーを設定するほどグローバルに識別する必要性が低い場合、何を主キーとして選択するかは結構難しい課題だと思ってます。
今回の例は「注文」なので「注文番号」のようなキーはありそうですが、トランザクション系のテーブルだと登録日時を主キーに含めたりしますよね？（え、しない？ｗ