データベースでのタイムスタンプ保存:DATETIME vs INT vs BIGINT

タイムスタンプを保存する 3 つの方法

ほとんどのデータベースは少なくとも 3 つの選択肢を提供します:ネイティブの datetime 型(TIMESTAMP・DATETIME・TIMESTAMPTZ)、単純な整数(INT または BIGINT)、文字列(VARCHAR)です。それぞれ、保存サイズ・クエリの扱いやすさ・タイムゾーン処理・将来への備えで異なるトレードオフがあります。多くのプロダクトのデータベースでは、ネイティブの datetime 列が最良の既定です。データベースが値を匿名の数値ではなく時間として比較・インデックス・切り詰め・グループ化・整形できるからです。

• ネイティブ datetime 型 — 日付算術・タイムゾーン変換・可読性に最適
• BIGINT 整数 — 高スループットの挿入と単純な数値範囲クエリに適する
• VARCHAR 文字列 — ほぼ常に誤り:日付の文字列比較は厳密な ISO 8601 形式でのみ機能する
• INT 整数 — 2038 年の境界を完全に確認していない限り、将来向けのタイムスタンプには避ける

MySQL:TIMESTAMP vs DATETIME vs INT

MySQL には似て見えて挙動が大きく異なる 2 つの日時型があり、一方には厳しい有効期限があります。TIMESTAMP は UTC とセッションタイムゾーンの自動変換が欲しいときに便利ですが、歴史的な 32 ビット範囲のため、将来向けのプロダクトデータには危険です。DATETIME は与えた日時をそのまま保存し、アプリが書き込み前に UTC に統一する場合は通常より明確です。

• TIMESTAMP:内部で 32 ビット Unix 秒として保存 — 1970-01-01 から 2038-01-19 に制限
• TIMESTAMP:挿入/読み取り時に UTC とセッションタイムゾーンの間で自動変換
• DATETIME:日時をそのまま保存、タイムゾーンなし。範囲は 1000-01-01 から 9999-12-31。Y2038 の影響を受けない。
• DATETIME:タイムゾーンを変換しない — UTC はアプリケーションレベルで制御する
• 推奨:2038 年の制限を避けるため、新しいテーブルには明示的な UTC 値で DATETIME を使う

PostgreSQL:TIMESTAMPTZ が正しい選択

PostgreSQL の TIMESTAMP WITH TIME ZONE(TIMESTAMPTZ)は、タイムスタンプを内部で UTC マイクロ秒として保存し、出力時にセッションタイムゾーンへ変換します。実時間の瞬間を表すため、多くのユースケースで最も安全かつ正しい選択肢です。名前は誤解を招きえます:TIMESTAMPTZ は America/New_York のような元のタイムゾーン ラベルを保存しません。瞬間を保存し、現在のセッションタイムゾーンに従って表示します。

• TIMESTAMPTZ:UTC を保存し、出力時にセッションタイムゾーンへ変換 — 移植性が高く夏時間に安全
• TIMESTAMP(タイムゾーンなし):リテラル値を変換せず保存 — タイムゾーン非依存のデータにのみ使う
• EXTRACT(EPOCH FROM col):任意の TIMESTAMP 列から Unix 秒を float として返す
• TO_TIMESTAMP(epoch):Unix 秒を TIMESTAMPTZ に戻す

インデックスとクエリ性能

通常のアプリケーションテーブルでは、ネイティブ datetime 列と BIGINT エポック列の性能差が決め手になることはまれです。クエリの形・インデックス設計・パーティション・行数のほうが重要です。まず意味が正しく保たれる型を選び、次にアプリが実際に実行する範囲クエリ向けにインデックスを張ってください。

• 3 つの型すべてが B-tree インデックスと効率的な範囲クエリをサポートする
• BIGINT 整数は、非常に大量のテーブルでの等価・範囲スキャンでわずかに速い
• ネイティブ datetime 型は日付部分のインデックス クエリを許す:WHERE created_at::date = '2024-01-01'
• VARCHAR タイムスタンプは性能が最悪 — 文字列比較は日付を理解しない

BIGINT のエポック保存が理にかなうとき

BIGINT は、データがイベント的で、追記が多く、すでに別システムが Unix 時間として生成している場合に妥当です。分析パイプライン・テレメトリ ストリーム・キュー・コンパクトなバイナリプロトコルは、数値が比較が速く言語に依存しないため、エポックミリ秒をよく使います。トレードオフは可読性です:人にはコンバーターが必要で、SQL の日付算術はより冗長になります。

• JavaScript クライアントがイベントを直接生成するなら Unix ミリ秒に BIGINT を使う
• ソースシステムが Unix 系で秒精度で十分なら Unix 秒に BIGINT を使う
• 単位を列名に文書化する:created_at_ms は created_at_epoch より明確
• アナリストに読みやすい SQL クエリが必要なら、生成 datetime 列を追加する
• 32 ビットの範囲制限のため、将来向けの現代的なタイムスタンプには INT を避ける

推奨されるスキーマ パターン

ほとんどの Web アプリでは、瞬間を UTC で保存し、ローカルの壁時計の意図を再構築する必要があるときだけ、ユーザーの希望タイムゾーンを別に保存します。America/New_York の午前 9:00 に予定された会議は、正確な UTC の瞬間に作成されたイベントログとは異なります。これらのケースは別々にモデル化してください。

• イベントログ:PostgreSQL では created_at TIMESTAMPTZ、MySQL では UTC の created_at DATETIME
• JavaScript イベント取り込み:created_at_ms BIGINT と明確な API ドキュメント
• 繰り返しのローカルスケジュール:local_date・local_time・timezone_id を保存し、次の瞬間を計算する
• 失効タイムスタンプ:expires_at をネイティブ datetime、または明示的な Unix 秒の expires_at_seconds に
• 監査テーブル:読みやすいデバッグのため created_at と updated_at の両方をネイティブ datetime 列に保つ

データベースのタイムスタンプ FAQ

データベースには UTC とローカル時刻のどちらを保存すべき?

イベントのタイムスタンプには UTC を保存し、表示時にローカル時刻へ変換します。繰り返し会議や営業時間など、ユーザーのローカルな壁時計の意図が重要なときは、タイムゾーン識別子を別に保存します。

BIGINT は TIMESTAMP より優れている?

一般的にはそうではありません。BIGINT は数値のエポックパイプラインに有用ですが、ネイティブ datetime 型のほうが SQL の日付算術・読みやすいデバッグ・タイムゾーン対応の出力に容易です。

MySQL は TIMESTAMP と DATETIME のどちらを使うべき?

新しいアプリケーションテーブルでは、2038 年の範囲制限を避け、セッションタイムゾーン変換に暗黙的に依存しないため、UTC 値の DATETIME がしばしばより安全です。

タイムスタンプは UTC で保存すべき?タイムゾーン付きで保存すべき?

瞬間を UTC で保存し(PostgreSQL は TIMESTAMPTZ、MySQL は UTC 値の DATETIME)、表示時にローカル時刻へ変換します。繰り返し会議など、ユーザーのローカルな壁時計の意図を再構築する必要があるときだけ、別の IANA タイムゾーン列を保ちます。