PostgreSQL のタイムゾーン処理の落とし穴 (timestamp VS timestamptz)

はじめに

PostgreSQLで日時を扱う際に、timestamp(timestamp without time zone) とtimestamptz(timestamp with time zone)
のどちらを使うべきかを迷ったことはありませんか？

タイムゾーンに関する問題を通じて、両者の違いについて解説します。

プロジェクトの例

例として、ポイント付与システムにおいて、ユーザーの登録日時に基づいてポイントを付与するロジックを実装します。

環境構成

• Database: PostgreSQL (Docker container)
• アプリサーバー: NestJS + TypeORM
• 開発環境(ローカル): macOS (JST)
• 本番環境: AWS ECS (UTC)

PostgreSQL のテーブル設計

ポイント付与ルールを管理するテーブルの設計は以下の通りです。TypeORM の Entity として定義されています。

@Entity('points_rules')
export class PointsRule {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ type: 'timestamp', name: 'start_date' })
  startDate: Date;

  @Column({ type: 'timestamp', name: 'end_date' })
  endDate: Date;

  @Column({ type: 'integer' })
  points: number;
}

以下の SQL に相当します。

CREATE TABLE points_rules (
    id SERIAL PRIMARY KEY,
    start_date TIMESTAMP WITHOUT TIME ZONE,
    end_date TIMESTAMP WITHOUT TIME ZONE,
    points INTEGER
);

テーブルのデータ

以下のポイント付与ルールがあります。timestamp を使用しているので、 timezone の情報がありません。

id	start_date	end_date	points
1	2025-05-01 00:00:00	2025-05-31 23:59:59	1500
2	2025-06-01 00:00:00	2025-06-30 23:59:59	1000

問題の発生

2025-06-01 08:00:00 (JST) に登録したユーザーは、1500ポイントと1000ポイントのどちらが付与されるでしょうか？

答えは「環境によって異なる」でした。その原因はなんでしょうか？

TypeORMでのクエリ実行

ユーザーが2025-06-01 08:00:00 (JST)に登録した場合を考えてみましょう。TypeORMを使用すると、
この時刻は2025-05-31T23:00:00.000Z（UTC）として処理されます。

SELECT id, start_date, end_date, points FROM points_rules
WHERE start_date <= '2025-05-31T23:00:00.000Z'
    AND end_date >= '2025-05-31T23:00:00.000Z';

ローカル環境（JST）での結果

6月のルール（1000ポイント）が適用されます。

{
  "id": 2,
  "startDate": "2025-05-31T15:00:00.000Z",
  "endDate": "2025-06-30T14:59:59.000Z",
  "points": 1000
}

本番環境（UTC）での結果

5月のルール（1500ポイント）が適用されます。

{
  "id": 1,
  "startDate": "2025-05-01T00:00:00.000Z",
  "endDate": "2025-05-31T23:59:59.000Z",
  "points": 1500
}

問題の原因

同じクエリを実行しているにも関わらず、異なる結果が返される原因は timestamp のセッション依存性にあります。

timestamp の問題点

timestampカラムの問題点は、クライアントセッションのタイムゾーンに依存することです。

• タイムゾーン情報を持たないため、セッションのタイムゾーン設定が解釈の基準となります。
• 同じデータベース値でも、異なる環境では異なる絶対時刻として解釈されます。

具体的に、同じデータベース値 2025-06-01 00:00:00 でも、以下のように解釈されます。

• JST環境では：2025-05-31 15:00:00Zとして解釈されます。
• UTC環境では：2025-06-01 00:00:00Zとして解釈されます。

結果として、絶対時間が9時間ずれるという問題が発生します。

環境別の timestamp データの解釈結果

以下の表は、データベースに保存されている値が各環境でどのように解釈されるかを示しています。

データベース値	ローカル環境（JST）での解釈	本番環境（UTC）での解釈
2025-05-01 00:00:00	2025-04-30 15:00:00Z(JST の5月1日)	2025-05-01 00:00:00Z(UTC の5月1日)
2025-05-31 23:59:59	2025-05-31 14:59:59Z(JST の5月31日)	2025-05-31 23:59:59Z(UTC の5月31日)
2025-06-01 00:00:00	2025-05-31 15:00:00Z(JST の6月1日)	2025-06-01 00:00:00Z(UTC の6月1日)
2025-06-30 23:59:59	2025-06-30 14:59:59Z(JST の6月30日)	2025-06-30 23:59:59Z(UTC の6月30日)

原因の特定

2025-06-01 08:00:00 (JST)に登録したユーザーの場合：

• JST環境：2025-06-01 08:00:00 +09:00(JST の6月1日)として認識し、6月のルールが適用されます。
• UTC環境：2025-05-31 15:00:00Z(UTC の5月31日)として認識し、5月のルールが適用されます。

この差異により、同じユーザーが異なる環境では異なるポイントが付与されます。

解決策：timestamptzを使用

改善されたテーブル設計

@Entity('points_rules')
export class PointsRule {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ type: 'timestamptz', name: 'start_date' })
  startDate: Date;

  @Column({ type: 'timestamptz', name: 'end_date' })
  endDate: Date;

  @Column({ type: 'integer' })
  points: number;
}

以下の SQL に相当します。

CREATE TABLE points_rules (
    id SERIAL PRIMARY KEY,
    start_date TIMESTAMP WITH TIME ZONE,  -- タイムゾーン情報を含む
    end_date TIMESTAMP WITH TIME ZONE,    -- タイムゾーン情報を含む
    points INTEGER
);

timestamptz使用時のデータ

id	start_date	end_date	points
1	2025-04-30 15:00:00+00	2025-05-31 14:59:59+00	1500
2	2025-05-31 15:00:00+00	2025-06-30 14:59:59+00	1000

timestamptzでは、タイムゾーン情報（ここではUTC）がデータと一緒に保存されます。

timestamptzの利点

timestamptzを使用すると、データの解釈はセッションのタイムゾーン設定に 依存しません。

環境別の timestamptz データの解釈結果は以下の通りです。

データベース値	ローカル環境での表示	本番環境での表示	UTC値
2025-04-30 15:00:00+00	2025-05-01 00:00:00+09:00	2025-04-30 15:00:00Z	2025-04-30 15:00:00Z
2025-05-31 14:59:59+00	2025-05-31 23:59:59+09:00	2025-05-31 14:59:59Z	2025-05-31 14:59:59Z
2025-05-31 15:00:00+00	2025-06-01 00:00:00+09:00	2025-05-31 15:00:00Z	2025-05-31 15:00:00Z
2025-06-30 14:59:59+00	2025-06-30 23:59:59+09:00	2025-06-30 14:59:59Z	2025-06-30 14:59:59Z

• 絶対時間が一致: どの環境でも同じUTC時刻として認識されます。
• 環境非依存: 開発環境と本番環境で同じ結果を得られます。
• 一貫した比較結果: 2025-05-31T23:00:00Zは常に同じルールにマッチします。

問題の解決

2025-06-01 08:00:00 (JST) に登録したユーザーは、どの環境でも、同じ結果として1000ポイントが付与されます。

{
  "id": 2,
  "startDate": "2025-05-31T15:00:00.000Z",
  "endDate": "2025-06-30T14:59:59.000Z",
  "points": 1000
}

まとめ

timestamp vs timestamptz の比較は以下の通りです。

項目	timestamp	timestamptz
保存方式	日時をそのまま保存	内部的にUTCで保存
クライアント依存性	❌ クライアントタイムゾーンの影響を受ける	✅ クライアントタイムゾーンに依存しない
比較動作	セッションタイムゾーンを使用して解釈	絶対的なUTC時刻で比較
環境間の一貫性	❌ 環境によって異なる結果	✅ どの環境でも同じ結果
推奨度	❌ グローバル時刻が必要な場合は非推奨	✅ 推奨

複数の環境で動作するシステムでは、timestamptzを使用することで、予期しない動作を回避できます。