Guide

データベースのタイムスタンプ: ストレージ、インデックス作成、および変換ガイド

このページを作成する理由

タイムスタンプは、イベントの順序付け、範囲のフィルタリング、ログの結合など、ほぼすべてのクエリを実行します。問題は、データベースごとに時間の処理方法が異なることです。このガイドでは、高速デシジョンツリー、すぐに出荷できるテーブルスキーマ、MySQL、PostgreSQL、SQLite の変換に加えて、データをデバッグする必要がある場合の完全なコンバータへの CTA も提供します。

素早い決断

常に UTC で保存します。エッジでのみローカルにフォーマットします。
精度: 分析には数秒で十分です。ログ/トレースの場合はミリ秒 (またはそれ以上)。
タイプ: 利用可能な場合はタイムゾーン対応タイプを使用します。相互運用性が重要な場合は、「BIGINT」エポックに戻ります。
インデックス: 列を関数でラップすることは避けてください。必要に応じて派生列を事前計算します。
保持: インデックスが肥大化する前に、古いデータを分割または削除します。

エンジン別の推奨タイプ

MySQL

推奨: UTC として保存された TIMESTAMP (範囲 1970 ～ 2038) または DATETIME (より広い範囲)。
より高い精度または 2038 年以降の将来の日付の場合: ミリ秒またはマイクロ秒単位の「BIGINT」エポック。
表の抜粋:

CREATE TABLE events (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  occurred_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  occurred_at_ms BIGINT GENERATED ALWAYS AS (UNIX_TIMESTAMP(occurred_at) * 1000) STORED,
  INDEX idx_events_occurred_at (occurred_at)
);

PostgreSQL

推奨: TIMESTAMPTZ (UTC を保存し、タイムゾーンでレンダリングします)。
精度: 最大マイクロ秒。エポックの GENERATED ALWAYS 列をサポートします。

CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  occurred_at_ms BIGINT GENERATED ALWAYS AS (EXTRACT(EPOCH FROM occurred_at) * 1000)::BIGINT STORED,
  INDEX (occurred_at)
);

SQLite

TEXT、REAL、または INTEGER として保存します。一貫性を保つために「INTEGER」エポックを選択してください。

CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  occurred_at_ms INTEGER NOT NULL, -- store UTC milliseconds
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_events_occurred_at_ms ON events (occurred_at_ms);

エポック ↔ 人間時間の変換

MySQL

-- epoch (seconds) -> timestamp
SELECT FROM_UNIXTIME(1704067200) AS utc_time;
-- timestamp -> epoch seconds/milliseconds
SELECT UNIX_TIMESTAMP(occurred_at) AS epoch_s,
       UNIX_TIMESTAMP(occurred_at) * 1000 AS epoch_ms
FROM events;
-- apply timezone safely
SELECT CONVERT_TZ(occurred_at, 'UTC', 'America/New_York') FROM events;

PostgreSQL

-- epoch seconds -> timestamptz (UTC)
SELECT to_timestamp(1704067200) AT TIME ZONE 'UTC';
-- timestamptz -> epoch seconds/milliseconds
SELECT EXTRACT(EPOCH FROM occurred_at) AS epoch_s,
       (EXTRACT(EPOCH FROM occurred_at) * 1000)::BIGINT AS epoch_ms
FROM events;
-- render in a specific zone
SELECT occurred_at AT TIME ZONE 'America/New_York' FROM events;

SQLite

-- epoch milliseconds -> UTC datetime string
SELECT datetime(occurred_at_ms / 1000, 'unixepoch') AS utc_time FROM events;
-- UTC datetime string -> epoch milliseconds
SELECT strftime('%s', '2024-12-01 00:00:00') * 1000 AS epoch_ms;

テスト中に高精度のコンバーターが必要ですか? Unix タイムスタンプコンバータ または バッチタイムスタンプコンバータ にジャンプします。

インデックス作成とクエリのパターン

範囲フィルター (WHERE happens_at >= ... AND happens_at < ...) を使用して、検索可能性を維持します。
フィルター内で列を DATE()、CAST()、または ::date でラップすることは避けてください。日レベルのグループ化が必要な場合は、計算/生成された列を使用してください。
ホットパーティションの場合、両方でフィルタリングする場合は複合インデックス (occurred_at, user_id) を考慮してください。
パーティション化/保持: 最近のパーティションを小さく保ちます。古いパーティションを安価なストレージにアーカイブします。
順序付け: 先頭の列が occurred_at の場合、ORDER BYoccurred_at DESC LIMIT 100 はインデックスに適しています。

よくある落とし穴 (および修正)

2038 制限 (MySQL TIMESTAMP): 将来の日付の行には DATETIME(3) またはエポック BIGINT を使用します。
セッションタイムゾーンドリフト: 接続開始時に time_zone='+00:00' (MySQL) または SET TIMEZONE='UTC'; (PostgreSQL) を設定します。
混合精度: 秒とミリ秒を同じ列に保存しないでください。摂取時に長さを強制します。
関数でラップされたフィルター: WHERE DATE(occurred_at)=... はインデックスを無効にします。代わりに「occurred_at_date」を事前計算します。
レポートにおける DST のサプライズ: UTC を保存します。実際の IANA ゾーンを使用してビューレイヤーでローカライズします。

移行チェックリスト

書き込みをフリーズするか、デュアル書き込みレイヤーを介してルーティングします。
新しい UTC 列を追加 (例: occurred_at_utc TIMESTAMPTZ または occurred_at_ms BIGINT)。
確定的変換による バックフィル。スポットチェックで確認します。
新しい列に インデックスを追加し、読み取りをそれに切り替えます。
モニタリングとバックフィルの検証後、レガシー列を削除します。

よくある質問

エポックまたは日時として保存する必要がありますか? エポック BIGINT は相互運用性と精度に優れています。 TIMESTAMPTZ は、組み込みの日付計算と読みやすさの点でより安全です。
どの精度を選択すればよいですか? ログ/トレース: ミリ秒以上。ビジネスデータ/分析: 通常は数秒で十分です。
ユーザーによる現地時間の送信を停止するにはどうすればよいですか? エッジで正規化します。タイムゾーンを含む入力を受け入れ、UTC に変換し、UTC のみを保存します。
受信タイムスタンプを検証するにはどうすればよいですか? 数字のみのペイロード、長さに基づく精度検出、および適切な範囲 (例: 2000-01-01 から 2100-01-01) を適用します。 タイムスタンプの精度レベル を参照してください。