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 :


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

PostgreSQL

Préféré : TIMESTAMPTZ (stocke UTC, affiche avec fuseau).
Précision : jusqu’aux microsecondes ; colonnes GENERATED ALWAYS pour l’epoch.


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

SQLite

Stocke en TEXT, REAL ou INTEGER ; choisir INTEGER epoch pour la cohérence.


SQL
1CREATE TABLE events (
2  id INTEGER PRIMARY KEY,
3  occurred_at_ms INTEGER NOT NULL, -- store UTC milliseconds
4  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
5);
6CREATE INDEX idx_events_occurred_at_ms ON events (occurred_at_ms);

Conversion epoch ↔ temps lisible

MySQL


SQL
1-- epoch (seconds) -> timestamp
2SELECT FROM_UNIXTIME(1704067200) AS utc_time;
3-- timestamp -> epoch seconds/milliseconds
4SELECT UNIX_TIMESTAMP(occurred_at) AS epoch_s,
5       UNIX_TIMESTAMP(occurred_at) * 1000 AS epoch_ms
6FROM events;
7-- apply timezone safely
8SELECT CONVERT_TZ(occurred_at, 'UTC', 'America/New_York') FROM events;

PostgreSQL


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

SQLite


SQL
1-- epoch milliseconds -> UTC datetime string
2SELECT datetime(occurred_at_ms / 1000, 'unixepoch') AS utc_time FROM events;
3-- UTC datetime string -> epoch milliseconds
4SELECT 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

Geler les écritures ou passer par un dual‑write.
Ajouter une colonne UTC (ex. occurred_at_utc TIMESTAMPTZ ou occurred_at_ms BIGINT).
Backfiller avec conversion déterministe ; vérifier avec des échantillons.
Indexer la nouvelle colonne puis basculer les lectures.
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

Pourquoi cette page

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 :


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

PostgreSQL

Préféré : TIMESTAMPTZ (stocke UTC, affiche avec fuseau).
Précision : jusqu’aux microsecondes ; colonnes GENERATED ALWAYS pour l’epoch.


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

SQLite

Stocke en TEXT, REAL ou INTEGER ; choisir INTEGER epoch pour la cohérence.


SQL
1CREATE TABLE events (
2  id INTEGER PRIMARY KEY,
3  occurred_at_ms INTEGER NOT NULL, -- store UTC milliseconds
4  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
5);
6CREATE INDEX idx_events_occurred_at_ms ON events (occurred_at_ms);

Conversion epoch ↔ temps lisible

MySQL


SQL
1-- epoch (seconds) -> timestamp
2SELECT FROM_UNIXTIME(1704067200) AS utc_time;
3-- timestamp -> epoch seconds/milliseconds
4SELECT UNIX_TIMESTAMP(occurred_at) AS epoch_s,
5       UNIX_TIMESTAMP(occurred_at) * 1000 AS epoch_ms
6FROM events;
7-- apply timezone safely
8SELECT CONVERT_TZ(occurred_at, 'UTC', 'America/New_York') FROM events;

PostgreSQL


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

SQLite


SQL
1-- epoch milliseconds -> UTC datetime string
2SELECT datetime(occurred_at_ms / 1000, 'unixepoch') AS utc_time FROM events;
3-- UTC datetime string -> epoch milliseconds
4SELECT 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

Geler les écritures ou passer par un dual‑write.
Ajouter une colonne UTC (ex. occurred_at_utc TIMESTAMPTZ ou occurred_at_ms BIGINT).
Backfiller avec conversion déterministe ; vérifier avec des échantillons.
Indexer la nouvelle colonne puis basculer les lectures.
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.