MakeTimestamp logo
MakeTimestamp

Guides

Timestamps en bases de données : guide de stockage, d’indexation et de conversion

Pourquoi cette page

Les timestamps pilotent presque toutes les requêtes : trier des événements, filtrer des plages, joindre des logs. Le problème : chaque base gère le temps différemment. Ce guide fournit des décisions rapides, des schémas prêts à l’emploi et des conversions pour MySQL, PostgreSQL et SQLite—ainsi que des liens vers les convertisseurs pour le debug.

Décisions rapides

  • Toujours stocker en UTC ; convertir en local uniquement à l’affichage.
  • Précision : secondes pour l’analytics ; millisecondes (ou plus) pour logs/traces.
  • Type : utiliser des types avec fuseau si disponibles ; sinon epoch BIGINT pour l’interop.
  • Index : éviter de wraper la colonne avec des fonctions ; pré‑calculer si besoin.
  • Rétention : partitionner ou purger avant que les index ne gonflent.

Types recommandés par moteur

MySQL

  • Préféré : TIMESTAMP (1970‑2038) ou DATETIME (plage plus large) stocké en UTC.
  • Pour plus de précision ou dates futures : epoch BIGINT en ms ou µs.
  • Exemple de table :
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

  • Préféré : TIMESTAMPTZ (stocke UTC, affiche avec fuseau).
  • Précision : jusqu’aux microsecondes ; colonnes GENERATED ALWAYS pour l’epoch.
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

  • Stocke en TEXT, REAL ou INTEGER ; choisir INTEGER epoch pour la cohérence.
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);

Conversion epoch ↔ temps lisible

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;

Besoin d’un convertisseur précis ? Unix Timestamp Converter ou Batch Timestamp Converter.

Indexation et patterns de requêtes

  • Utiliser des filtres de plage (WHERE occurred_at >= ... AND occurred_at < ...) pour rester sargable.
  • Éviter DATE(), CAST() ou ::date dans les filtres ; utiliser des colonnes calculées.
  • Pour les partitions chaudes, envisager un index composite (occurred_at, user_id).
  • Partitionnement/rétention : garder les partitions récentes petites ; archiver les anciennes.
  • Tri : ORDER BY occurred_at DESC LIMIT 100 est efficace si occurred_at est en tête.

Pièges courants (et correctifs)

  • Limite 2038 (MySQL TIMESTAMP) : utiliser DATETIME(3) ou epoch BIGINT.
  • Dérive de fuseau de session : fixer time_zone='+00:00' (MySQL) ou SET TIMEZONE='UTC'; (PostgreSQL).
  • Précision mixte : ne pas mélanger secondes et millisecondes ; valider la longueur à l’ingestion.
  • Filtres avec fonctions : WHERE DATE(occurred_at)=... désactive les index ; pré‑calculer occurred_at_date.
  • Surprises DST en reporting : stocker UTC ; localiser côté vue avec des zones IANA.

Checklist de migration

  1. Geler les écritures ou passer par un dual‑write.
  2. Ajouter une colonne UTC (ex. occurred_at_utc TIMESTAMPTZ ou occurred_at_ms BIGINT).
  3. Backfiller avec conversion déterministe ; vérifier avec des échantillons.
  4. Indexer la nouvelle colonne puis basculer les lectures.
  5. Supprimer l’ancienne colonne après monitoring.

FAQ

  • Epoch ou datetime ? BIGINT epoch est idéal pour l’interop et la précision ; TIMESTAMPTZ est plus sûr pour les calculs et la lisibilité.
  • Quelle précision choisir ? Logs/traces : ms ou plus. Données métier/analytics : secondes suffisent souvent.
  • Comment empêcher l’envoi d’heure locale ? Normaliser à la périphérie : accepter avec fuseau, convertir en UTC, stocker UTC.
  • Comment valider les timestamps entrants ? Autoriser uniquement des chiffres, détecter la précision par longueur et imposer une plage (ex. 2000-01-01 à 2100-01-01). Voir Timestamp Precision Levels.

Outils et guides associés