【テクニカル・上級編】日付・時刻型 (Date/Time Types) – PostgreSQL

PostgreSQLの日付・時刻型：その「型」の選択が、数年後の負債を左右する話

PostgreSQLを長年触っていると、若手エンジニアから「とりあえず `timestamp` を使っておけばいいですよね？」という質問を受けることがよくあります。

その度に、私は少しだけ苦い顔をしてしまう。もちろん、動かないことはない。けれど、データベースエンジニアとして、その「とりあえず」が後々どれほど大きな技術的負債を抱えることになるかを知っているからです。

今日は、PostgreSQLにおける日付・時刻型の「正しい選び方」と、その裏側にあるアーキテクチャの話をしようと思います。

—

1. `timestamp` vs `timestamptz`：決定的な境界線

まず、これだけは覚えて帰ってください。「特別な理由がない限り、`timestamptz` (timestamp with time zone) を使うべきだ」という原則です。

多くの人が誤解しているのですが、`timestamptz` はタイムゾーン情報を保存しているわけではありません。内部的には、すべて UTC（協定世界時）の8バイト整数 として格納されています。

`timestamp` (without time zone): クライアントが投げた時刻をそのまま記録します。いわば「壁掛け時計」の時刻です。
`timestamptz`: クライアントが投げた時刻を一旦UTCに変換して格納し、読み出す時には接続先の `TimeZone` 設定に合わせて再変換します。

なぜ `timestamptz` が優れているのか。それは、アプリケーション側でタイムゾーン変換のロジックを書かなくて済むからです。アプリケーションのコード内で `DateTime.now()` を扱うのは、バグの温床でしかありません。データベースの層でタイムゾーンを吸収させる。これが、スケーラブルなシステムを作るための「作法」です。

2. インデックス設計と「SARGability」の罠

日付型を扱う際、パフォーマンスのボトルネックになりやすいのがインデックスの活用です。特に `WHERE` 句での記述には注意が必要です。

例えば、ある日のデータを抽出するために以下のようなクエリを書いたことはありませんか？

— アンチパターン
SELECT FROM logs WHERE date_trunc(‘day’, created_at) = ‘2023-10-01’;

これはインデックスが効きません。`date_trunc` という関数を噛ませた時点で、PostgreSQLはテーブルフルスキャンを選択せざるを得ないのです。

ベストプラクティス:
範囲指定（Range Query）を使用すること。

— 推奨
SELECT FROM logs
WHERE created_at >= ‘2023-10-01’ AND created_at < '2023-10-02'; これなら、`created_at` に貼られたB-treeインデックスを完璧に活用できます。実行計画（EXPLAIN）を確認すれば一目瞭然ですが、余計な関数を通さないだけで、クエリのレスポンスは桁違いに速くなります。

3. `interval` 型の強力な実用性

意外と見落とされがちなのが `interval` 型です。これ、実は計算エンジンとして非常に優秀なんです。

「1ヶ月前のデータを消したい」という時、アプリケーション側で「今日が31日か30日か」を計算するのはナンセンスです。

— 非常に直感的で、かつ正確
DELETE FROM logs WHERE created_at < NOW() - INTERVAL '1 month'; PostgreSQLの `interval` は、閏年や月の日数変動を内部で巧みに処理してくれます。この「日付演算の複雑さをエンジンに丸投げできる」という点は、PostgreSQLを信頼する大きな理由の一つです。

4. 運用の現場で起きる「ハマりどころ」

最後に、トラブルシューティングの観点を一つ。

PostgreSQLにおける時刻の扱いにおいて、最大の敵は「システムのタイムゾーン設定の不一致」です。サーバーのOS設定、DBインスタンスの `timezone` 設定、そしてアプリケーションの接続設定。これらがズレていると、`timestamptz` を使っていても「なぜか9時間ずれる」といった怪奇現象に悩まされることになります。

私の経験上、最も安全なのは以下のルールです。
1. サーバーの時刻は常に UTC で統一する。
2. アプリケーションの接続時にも、明示的に `SET timezone = ‘UTC’` を投げる（あるいは接続文字列で指定する）。
3. 表示が必要な時だけ、フロントエンド層でローカルタイムに変換する。

—

まとめ：型を知ることは、未来を知ること

日付や時刻は、システムにとっての「血流」です。ここが曖昧だと、ログの時系列解析も、期限管理も、すべてが狂います。

最初は「ただの時刻保存」に見えるかもしれませんが、PostgreSQLの内部アーキテクチャを理解し、適切な型を選択し、インデックスが効くクエリを叩く。この積み重ねが、数年後に「このシステムは非常に堅牢だ」と言われるための基礎工事になります。

次回の設計時、`timestamp` を選ぼうとしている指を一度止めて、ぜひ `timestamptz` を選択してみてください。その小さな決断が、将来のあなたを助けてくれるはずです。

さて、次は「`jsonb` のインデックス戦略」について語ろうか。また次回お会いしましょう。