PostgreSQLの日付・時刻型、ちゃんと使い分けてる？「沼」にハマらないための設計ガイド
やあ。今日はPostgreSQLの日付・時刻型の話をしよう。
「え、日付なんて `timestamp` で適当に突っ込んでおけばいいでしょ？」なんて思ってない？ もしそうなら、数年後の君が泣くことになるかもしれない。特にグローバル展開を視野に入れたアプリや、集計クエリを多用するシステムを作っているなら、ここは避けて通れない聖域なんだ。
現場でよく見る「なんとなく」の設計と、プロが選ぶ「確信を持った」設計の違いを、僕の経験を交えて解説していくよ。
—
1. timestamp vs timestamptz：最大の罠
まず、一番大切なことから。PostgreSQLには `timestamp`（Without Time Zone）と `timestamptz`（With Time Zone）があるよね。
結論から言うと、基本的には `timestamptz` 一択だと思っていい。
なぜか？ `timestamp` は「その値がどのタイムゾーンなのか」をDBが一切気にしないからだ。「2023-10-01 10:00:00」というデータが入っていても、それが日本時間なのかUTCなのか、DB側には知る由もない。
一方、`timestamptz` は違う。内部的には常にUTCで保存されるけれど、クライアントから読み出すときには、そのセッションの `timezone` 設定に合わせて「よしなに」変換してくれるんだ。
- `timestamp`: 「壁掛け時計」のイメージ。どこにいても表示は変わらない。
- `timestamptz`: 「地球上の絶対時刻」のイメージ。場所が変われば表示も変わる。
アプリのログや注文日時など、あとで時系列で並び替えたり集計したりするものは、迷わず `timestamptz` を選ぼう。
—
2. その他の型たち：適材適所を考える
`timestamp` 以外にも強力な武器がある。これらを適切に選ぶだけで、インデックスの効率やクエリの書きやすさがガラリと変わるよ。
- `date`: 時刻情報が不要な場合（誕生日や、日次の売上データなど）。`timestamp` で代用すると、日付検索のインデックスが効かなくなることがあるから注意が必要だ。
- `time`: 日付が不要な場合（営業開始時間や、バッチ処理の実行予定時刻など）。
- `interval`: これが一番面白い。`2023-10-01` に `3 days` を足すといった計算が直感的に書ける。
— 昨日の売上を集計する例
SELECT
FROM orders
WHERE created_at >= CURRENT_DATE – INTERVAL ‘1 day’;
このように `interval` を使うと、コードが驚くほど読みやすくなるよね。
—
3. 実務で遭遇する「落とし穴」への対策
A. アプリ側とDB側の時差問題
よくあるのが「DBはUTCなのに、アプリがJSTで送ってくる」というケース。これ、原因を特定するのに数時間溶かす羽目になるんだよね。
対策はシンプル。「DBはUTC運用」と決め打ちすること。 アプリ側から投げるデータもDB接続時のタイムゾーンも全部UTCに統一しておけば、悩みの大半は消える。
B. インデックスの設計
日付型でインデックスを貼る際、`to_char()` や `date_trunc()` を WHERE 句で使っていないかな？
— ダメな例：インデックスが効かない（フルスキャンになる）
SELECT FROM orders WHERE to_char(created_at, ‘YYYY-MM-DD’) = ‘2023-10-01’;
— 良い例：範囲検索にする
SELECT FROM orders
WHERE created_at >= ‘2023-10-01 00:00:00’
AND created_at < '2023-10-02 00:00:00';
関数を通すとインデックスが無視される。これはSQLを書く時の鉄則だ。
---
最後に：エンジニアとしての心得
日付の扱いは、一見地味だけど、システムの本質的な信頼性に直結する部分だ。
「とりあえずこれで動くからいいや」で済ませたコードは、数年後に必ず技術的負債として帰ってくる。設計段階で「このデータは物理的な瞬間に紐づくのか？ それとも単なる日付のラベルなのか？」を考える癖をつけてみてほしい。
もし迷ったら、いつでもDBの公式ドキュメントを開こう。PostgreSQLのドキュメントは、世界で一番信頼できる技術書だからね。
それじゃあ、また現場で会おう。いいクエリを書いてくれ！
コメント