PostgreSQL の timestamp with time zone 型にまつわる誤解

HQ ではメインのリレーショナルデータベースとして PostgreSQL を利用しており、 ほぼすべてのテーブルにはcreated_atやupdated_atのような作成日時や更新日時などを記録するカラムを持たせています。

今回は PostgreSQL のタイムスタンプ型について誤解しがちな点について説明します。

型名から想像するタイムスタンプ型の挙動

PostgreSQL には、タイムスタンプのデータ型として以下の 2 つがあります。

  • timestamp with time zone（省略形: timestamptz）
  • timestamp without time zone（省略形: timestamp）

一見とてもわかりやすい名前に見えますね。しかし実際の仕様・挙動は、多くの人がこれらの名前から想像するものとは異なるかもしれません。 私は初めて HQ の DB スキーマを見て timestamp with time zone が多く使われているのを見たとき、こんな風に考えました。

  • timestamp with time zoneはおそらく時間情報とタイムゾーン情報を一緒に保存できる型なんだろうな（間違い）
    • 各レコード・各カラムごとに異なるタイムゾーンを持たせることができるのかな（間違い）
    • Go の time.Time や Rails の ActiveSupport::TimeWithZone もタイムゾーン情報を持った時間型なので、DB からの読み込み・書き込み時にこれらとマッピングできる感じだろうか（間違い）
    • HQ では基本的にこっちを使っているっぽい
  • じゃあ、timestamp without time zoneは時間情報だけを保存できる型なんだろうな。こっちはほとんど使われてなさそう
  • でもタイムゾーンが必要になるのって、フロントエンドの UI でユーザーのローカルタイムゾーンで時間・日付を表示するときくらいじゃないだろうか
    • ユーザーのローカルタイムゾーンはブラウザと JavaScript が面倒を見てくれるし、「このユーザーのタイムゾーンはこれ」という情報をサーバー側に保存する必要もなさそう
    • サーバー側のビジネスロジックでタイムゾーンが必要な処理は今のところないし、 DB に保存する必要はあるんだろうか？

実際に保存されているtimestamp with time zoneなデータを見てみても以下のようになっており、 この+00を見て「なるほど、このレコードのこのカラムは UTC で保存されているっぽいな」と思い込んでいました。

core=> SELECT created_at_with_timezone FROM users LIMIT 1;
    created_at_with_timezone
-------------------------------
 2023-04-13 07:09:05.804634+00
(1 row)

ここまでの推測が仮に正しいとすると、タイムゾーンに関心がないサーバー側で timestamp without time zoneを使うのは無駄ではないかと思い、 仕様について調べたところ、実際には想像した挙動とまったく異なっていることがわかりました。

実際の timestamp with time zone の挙動

実はtimestamp with time zoneはその名前に反してタイムゾーン情報を保持していません。 内部的には常に UTC 時間として保存されています。

PostgreSQL: Documentation: 17: 8.5.1.3. Time Stamps

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT).

つまり、INSERT文で保存する値としてtimestamp with time zone '2024-01-01 00:00:00+09'を指定しようが、 timestamp with time zone '2023-12-31 15:00:00+00'を指定しようが内部的には全く同じ扱いになります。 カラム、レコード、テーブル単位で「このカラム、このレコード、このテーブルではこのタイムゾーン」のような挙動もなく、内部的には常に一貫して UTC で保存されています。

では先ほどのSELECTした結果の2023-04-13 07:09:05.804634+00の+00はなんだったのでしょうか？ これは「timestamp with time zoneの値を PostgreSQL クライアントにどのタイムゾーンで見せるか」というTimeZone設定 （サーバーの設定でデフォルト値が決まっているが、クライアントとのコネクションごとに変更可能）によって変換して出力されたものです。 つまり単なるプレゼンテーションの話であり、この出力は「timestamp with time zoneがどう保存されているか」とは関係ありません。

PostgreSQL: Documentation: 17: 8.5.1.3. Time Stamps

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.4).

PostgreSQL: Documentation: 17: 8.5.3. Time Zones

All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client.

以下のようにコネクションのタイムゾーン設定を変えることで、絶対時間は同一のまま異なるタイムゾーンで表示できることがわかります。

core=> SHOW TimeZone;
 TimeZone
----------
 UTC
(1 row)

core=> SELECT created_at_with_timezone FROM users LIMIT 1;
    created_at_with_timezone
-------------------------------
 2023-04-13 07:09:05.804634+00
(1 row)

core=> SET SESSION TIME ZONE 'Asia/Tokyo';
SET
core=> SELECT created_at_with_timezone FROM users LIMIT 1;
    created_at_with_timezone
-------------------------------
 2023-04-13 16:09:05.804634+09
(1 row)

timestamp without time zone はなんなのか

timestamp with time zoneの仕様はわかりました。 ではtimestamp without time zoneはどんなもので、なんのために存在するのでしょうか？

timestamp without time zone はその名前が示す通りタイムゾーン情報を持たないタイムスタンプ型であり、 なおかつ PostgreSQL クライアントとのやり取りにおいてもタイムゾーンに関する変換を行いません。

core=> SHOW TimeZone;
 TimeZone
----------
 UTC
(1 row)

core=> SELECT created_at_without_timezone FROM users LIMIT 1;
 created_at_without_timezone
----------------------------
 2024-10-03 10:42:50.639281
(1 row)

core=> SET SESSION TIME ZONE 'Asia/Tokyo';
SET
core=> SELECT created_at_without_timezone FROM users LIMIT 1;
 created_at_without_timezone
----------------------------
 2024-10-03 10:42:50.639281
(1 row)

これの用途としては、おそらくそのシステムのローカル時間で保存したい場合に使うものと思われます。 しかしその場合「そのローカル時間はどのタイムゾーンのものなのか」という設定情報が PostgreSQL の外側（大抵の場合 PostgreSQL クライアントとして動くアプリケーションプロセス）で管理されることになり、 データの整合性の面で大きな不安があります。 アプリケーションプロセスやシステムのタイムゾーン設定が変わると、データの解釈が変わってしまうということになりかねません。 そのため、レガシーシステムとの連携などの事情がない限り基本的にtimestamp with time zoneを使う方が良いでしょう。 timestamp with time zoneを採用し、これを適切に扱える PostgreSQL ドライバーや ORM を使っていれば、 PostgreSQL のTimeZone設定やアプリケーションプロセスのタイムゾーンが変わろうが絶対的な時間が誤って解釈されることはないはずです。

まとめ

  • なにか特殊な事情がない限りtimestamp with time zoneを使うのが良い
  • DB ドライバーや ORM を介してデータに触れる場合はこのあたりの事情は普段あまり意識しないが、「PostgreSQL はどのタイムスタンプ型でもタイムゾーン情報を保持しない」ということは覚えておきたい
  • 直接 SQL を書く場合、PostgreSQL サーバーに接続後まずはSET SESSION TIME ZONE 'Asia/Tokyo';を実行しておくと、以後のクエリでのタイムスタンプ表示がわかりやすくなる