PostgreSQLの日付型について備忘録

仕事で調べたことをメモしておきます。

年月日時刻を保存する場合につかうtimestamp型ですが、

(A) with time zone
(B) without time zone

の2通りがあります。

timestamp with time zone

DB内ではUTC(協定世界時)で保存。
呼び出しや書き込みの際は処理するクライアントの地域に合わせて勝手に時刻変換してくれる（日本だと +9時間）。
SELECTで引っ張り出した際には「2015-09-11 12:00:00+09」みたいになる。

検索条件も、日本からであれば下記の2つは同じ結果を返します。

SELECT * FROM TESTTABLE WHERE DATE='2015-09-11 12:00:00'
SELECT * FROM TESTTABLE WHERE DATE='2015-09-11 03:00:00 UTC'

日本時間からUTC時間を出すにはマイナス9時間⇒日本の12時とUTCの3時は同じ。

timestamp without time zone

DB内では言われた年月日日時を保存。
呼び出しや書き込みの際も同様。
SELECTで引っ張り出した際には「2015-09-11 12:00:00」みたいになる。

使い分け

(A)は、データを書きこむ処理が海外で動く想定の場合に好都合です。現地時刻ではなくUTCで記録が行われるので、データ投入時刻やログなどが実際に発生した時間の順に記録されたりします。ただ、DB処理を行うツールやドライバによっては対応していない場合があるようですので注意が必要です（実際、ODBC接続しか使えないシステムで、ODBC経由で接続しようとしてみたところ「対応していない型」のようなエラーが出てしまいました）。

(B)は、日本国内でしか利用が想定されないシステムの場合に利用可能です。また、現地時刻をあえて意識しなければならない場合もこちらです。