MakeTimestamp logo
MakeTimestamp

Guide

Zeitstempel in Datenbanken: Leitfaden zur Speicherung, Indizierung und Konvertierung

Warum diese Seite

Zeitstempel steuern fast jede Abfrage: Ereignisse ordnen, Bereiche filtern und Protokolle verknüpfen. Der Haken ist, dass jede Datenbank anders mit der Zeit umgeht. Dieser Leitfaden bietet Ihnen einen schnellen Entscheidungsbaum, versandfertige Tabellenschemata und Konvertierungen für MySQL, PostgreSQL und SQLite – sowie CTAs für die vollständigen Konverter, wenn Sie Daten debuggen müssen.

Schnelle Entscheidungen

  • Immer in UTC speichern; Nur am Rand auf lokal formatieren.
  • Präzision: Sekunden reichen für Analysen aus; Millisekunden (oder mehr) für Protokolle/Ablaufverfolgungen.
  • Typ: Zeitzonenfähige Typen verwenden, sofern verfügbar; Greifen Sie auf die BIGINT-Epoche zurück, wenn Interop der Schlüssel ist.
  • Indizes: Vermeiden Sie es, die Spalte in Funktionen einzuschließen. Berechnen Sie abgeleitete Spalten bei Bedarf vorab.
  • Aufbewahrung: Partitionieren oder bereinigen Sie alte Daten, bevor sich die Indizes aufblähen.

Empfohlene Typen nach Motor

MySQL

  • Bevorzugt: TIMESTAMP (Bereich 1970-2038) oder DATETIME (größerer Bereich), gespeichert als UTC. – Für höhere Präzision oder zukünftige Daten nach 2038: BIGINT-Epoche in Millisekunden oder Mikrosekunden.
  • Tabellenausschnitt:
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

  • Bevorzugt: TIMESTAMPTZ (speichert UTC, rendert mit Zeitzone).
  • Präzision: bis zu Mikrosekunden; unterstützt GENERATED ALWAYS-Spalten für Epochen.
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

  • Speichert als TEXT, REAL oder INTEGER; Wählen Sie aus Gründen der Konsistenz die Epoche 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);

Epoche ↔ menschliche Zeit umwandeln

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;

Benötigen Sie beim Testen einen präzisen Konverter? Wechseln Sie zu Unix-Zeitstempelkonverter oder Batch-Zeitstempelkonverter.

Indizierungs- und Abfragemuster

  • Verwenden Sie Bereichsfilter (WHERE occurred_at >= ... AND occurred_at < ...), um Sargable zu bleiben.
  • Vermeiden Sie es, die Spalte in Filtern in DATE(), CAST() oder ::date einzuschließen. Verwenden Sie berechnete/generierte Spalten, wenn Sie Gruppierungen auf Tagesebene benötigen.
    – Berücksichtigen Sie bei Hot-Partitionen die zusammengesetzten Indizes (occurred_at, user_id), wenn Sie nach beiden filtern.
  • Partitionierung/Aufbewahrung: Aktuelle Partitionen klein halten; Archivieren Sie alte Partitionen auf günstigerem Speicher.
  • Reihenfolge: ORDER BY occurred_at DESC LIMIT 100 ist indexfreundlich, wenn die führende Spalte occurred_at ist.

Häufige Fallstricke (und Lösungen)

  • Grenzwert 2038 (MySQL TIMESTAMP): Verwenden Sie DATETIME(3) oder Epoche BIGINT für in der Zukunft liegende Zeilen.
  • Zeitzonendrift der Sitzung: Legen Sie time_zone='+00:00' (MySQL) oder SET TIMEZONE='UTC'; (PostgreSQL) beim Verbindungsstart fest.
  • Gemischte Genauigkeit: Speichern Sie niemals Sekunden und Millisekunden in derselben Spalte. Erzwingen Sie die Länge der Einnahme.
  • Funktionsumhüllte Filter: WHERE DATE(occurred_at)=... deaktiviert Indizes; Berechnen Sie stattdessen occurred_at_date vor.
  • DST-Überraschungen in Berichten: UTC speichern; Lokalisierung in der Ansichtsebene mit echten IANA-Zonen.

Migrationscheckliste

  1. Schreibvorgänge einfrieren oder über eine Dual-Write-Schicht weiterleiten.
  2. Fügen Sie eine neue UTC-Spalte hinzu (z. B. occurred_at_utc TIMESTAMPTZ oder occurred_at_ms BIGINT).
  3. Backfill mit einer deterministischen Konvertierung; durch Stichproben überprüfen.
  4. Indizes hinzufügen für die neue Spalte, dann Lesevorgänge umschalten darauf.
  5. Legacy-Spalte entfernen nach Überwachung und Backfill-Verifizierung.

FAQ

  • Soll ich als Epoche oder Datum/Uhrzeit speichern? Epoche BIGINT eignet sich hervorragend für Interop und Präzision; TIMESTAMPTZ ist sicherer für die integrierte Datumsberechnung und Lesbarkeit.
  • Welche Genauigkeit soll ich wählen? Protokolle/Traces: ms oder höher. Geschäftsdaten/Analysen: Sekunden reichen normalerweise aus.
  • Wie verhindere ich, dass Benutzer die Ortszeit senden? Am Rand normalisieren: Eingaben mit Zeitzone akzeptieren, in UTC konvertieren und nur UTC speichern.
  • Wie validiere ich eingehende Zeitstempel? Erzwingen Sie Payloads, die nur aus Ziffern bestehen, eine längenbasierte Präzisionserkennung und einen angemessenen Bereich (z. B. 2000-01-01 bis 2100-01-01). Siehe Zeitstempel-Präzisionsstufen.

Verwandte Tools und Anleitungen