unixepochtime
← 記事一覧

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

By unixepochtime.com editorial team2026年5月16日Updated 2026年6月20日

タイムスタンプに誤った列型を選ぶと、タイムゾーンのずれ、2038 年オーバーフロー、壊れた範囲クエリ、分かりにくい API 出力を招きます。MySQL と PostgreSQL でネイティブの datetime 型・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

MongoDB stores dates natively as the BSON Date type — a signed 64-bit integer of milliseconds since the Unix epoch. The driver maps it to a Date object in JavaScript, datetime in Python, ISODate in the mongo shell.

  • BSON Date: 8 bytes, signed 64-bit, milliseconds since 1970 UTC
  • Indexes natively — range queries on Date are fast and idiomatic
  • Use ISODate("2024-01-01T00:00:00Z") in the shell to create comparable values
  • For multi-precision needs, use Decimal128 or a sub-document with seconds + nanos; Date itself is millisecond precision
  • TTL indexes operate on Date fields specifically — integer epoch fields will not work with expireAfterSeconds

DynamoDB: epoch attributes and TTL

DynamoDB has no dedicated date type. Two patterns dominate: ISO 8601 strings for human readability, or epoch numbers for compactness and TTL support.

  • TTL requires the attribute to be a Number representing Unix seconds (not milliseconds)
  • Strings store ISO 8601 with full timezone info but cost more bytes and sort lexicographically (so include the Z suffix consistently)
  • Range queries on a sort-key epoch number are extremely cheap and align with DynamoDB’s query model
  • Avoid mixing seconds and milliseconds across tables — DynamoDB will not protect you from the unit drift
  • DynamoDB Streams emit an ApproximateCreationDateTime in ISO 8601 regardless of how you stored the original value

FAQ

Should I store UTC or local time in a database?
Store UTC for event timestamps and convert to local time when displaying. Store a timezone identifier separately when the user's local wall-clock intent matters, such as recurring meetings or business hours.
Is BIGINT better than TIMESTAMP?
Not generally. BIGINT is useful for numeric epoch pipelines, but native datetime types are easier for SQL date arithmetic, readable debugging, and timezone-aware output.
Should MySQL use TIMESTAMP or DATETIME?
For new application tables, DATETIME with UTC values is often safer because it avoids the 2038 range limit and does not silently depend on session timezone conversion.
Should I store timestamps as UTC or with a timezone?
Store the instant in UTC (TIMESTAMPTZ in PostgreSQL, or DATETIME with UTC values in MySQL) and convert to local time on display. Keep a separate IANA timezone column only when you must reconstruct a user's local wall-clock intent, such as recurring meetings.
← ブログに戻るコンバーターを開く