timestamp with time zone型はタイムゾーン情報を持っていない

  • 31
    いいね
  • 0
    コメント
この記事は最終更新日から1年以上が経過しています。

絶対時刻と概念時刻

2015年1月1日00:00:00 という時刻表記は、地球上のどこで読んだかによって意味が変わってしまいます。このような時刻のことを 概念時刻 と呼ぶことにしましょう。ボジョレー・ヌーボーの解禁時刻、11月第3木曜日00:00:00も概念時刻だからこそ世界各地で解禁時刻が違い、バブル期は日本が世界で最初に解禁されるって盛り上がったものです。

2015年1月1日00:00:00(日本時間)、これなら 絶対時刻 です。世界のどこで読もうと、ある一瞬を意味します。これは2014年12月31日15:00:00(グリニッジ時間)と同じ瞬間を表しており、前者と後者は同じ瞬間の別表記に過ぎません。

timestamp with time zone

OracleおよびPostgreSQLのtimestamp with time zone型が格納する値はその名前に反し、絶対時刻です。 タイムゾーン情報はまったく含まれていません。
具体的には、マシンのタイムゾーン設定に関わらず必ずUTCで格納されます。
文字列、例えば'2015-01-01 00:00:00'をtimestamp with time zoneにキャストしようとすると、

  1. まずローカル時刻で解釈されます。 ⇒ 2015年1月1日00:00:00(日本時間)
  2. UTCベースで格納されます。 ⇒ 2014年12月31日15:00:00(グリニッジ時間)

これを文字列にキャストしようとすると、'2015-01-01 00:00:00+09' のようにローカルタイムゾーン表記で文字列化してくれます。

日時を絶対時刻で扱うのはアプリケーション側の処理系と相性が良いはずです。例えばJavaのDate型やそのサブクラスたちは絶対時刻を格納するデータ型です。

DB設計時、日時の格納には基本的にtimestamp with time zone型を使ってください。概念時刻を格納するtimestamp [ without time zone ]を使う機会はボジョレー・ヌーボーの解禁時刻くらいしかないはず。

MySQLでは

TIMESTAMP型が絶対時刻を格納し、DATETIME型が概念時刻を格納します。こいつらは精度も範囲も違うのですが。

SQL Serverでは

datetime型やdatetime2型が概念時刻を格納し、datetimeoffset型が、なんと概念時刻+タイムゾーンを格納します。