Timestamps Unix en base de données — stockage, requêtes et bonnes pratiques

La plupart des schémas de base de données doivent stocker des dates et des heures. Le choix se fait généralement entre stocker un timestamp Unix (secondes entières depuis 1970) ou utiliser un type de colonne datetime natif. Les deux fonctionnent. Les différences apparaissent quand vous requêtez, triez, comparez et affichez les données — et surtout dans les ennuis que vous pouvez avoir avec la gestion des fuseaux horaires.

Le Convertisseur de timestamp Unix gère les conversions ponctuelles. Cet article couvre les compromis au niveau base de données et la façon dont chaque base majeure gère les deux approches.

Entier vs colonne datetime native : le compromis central

Stockage en entier (timestamp Unix) :

Type de colonne : INT ou BIGINT
Stocke : le nombre de secondes (ou de millisecondes) depuis 1970-01-01 00:00:00 UTC
Fuseau horaire : aucun — l’entier est toujours en UTC
Calculs : simples (created_at > NOW_UNIX - 86400 pour “dernières 24 heures”)
Lisibilité : nécessite une conversion pour comprendre (1714000000 → il faut vérifier à quelle date cela correspond)

Stockage en datetime natif :

Type de colonne : TIMESTAMP, TIMESTAMPTZ, DATETIME selon la base
Stocke : une représentation structurée de date et d’heure
Fuseau horaire : varie selon le type et la base (voir ci-dessous)
Calculs : fonctions spécifiques à la base (DATEDIFF, EXTRACT, arithmétique d’intervalles)
Lisibilité : lisible dans un résultat de requête (2024-04-25 11:46:40)

Aucun choix n’est universellement “le bon”. Cela dépend de votre cas d’usage, de vos requêtes, et de la manière dont votre application gère les fuseaux horaires.

PostgreSQL : TIMESTAMP vs TIMESTAMPTZ vs entier

PostgreSQL propose trois options pratiques :

TIMESTAMP WITHOUT TIME ZONE — stocke exactement ce que vous lui donnez. Pas de conversion de fuseau. Si vous insérez '2024-04-25 09:00:00' depuis un serveur applicatif à New York, c’est ce qui est stocké — même si cela représente 13:00 UTC. À la lecture, vous récupérez 2024-04-25 09:00:00 sans contexte de fuseau. Dangereux dès que le fuseau serveur peut changer ou que plusieurs fuseaux sont impliqués.

TIMESTAMPTZ (TIMESTAMP WITH TIME ZONE) — stocke toujours en UTC en interne, quel que soit ce que vous fournissez. À l’écriture, PostgreSQL convertit depuis le fuseau de la session vers l’UTC. À la lecture, il reconvertit vers le fuseau de la session. C’est le type recommandé pour la plupart des usages en production. Son nom est trompeur — il ne stocke pas un fuseau ; il stocke de l’UTC et convertit.

-- Session timezone: America/New_York (UTC-4)
INSERT INTO events (created_at) VALUES ('2024-04-25 09:00:00');
-- Stored as: 2024-04-25 13:00:00 UTC

SELECT created_at FROM events;
-- Returns: 2024-04-25 09:00:00-04 (converted back to session timezone)

BIGINT (timestamp Unix) — totalement agnostique vis-à-vis des fuseaux. L’application est responsable de la conversion. Utile si vous voulez contourner entièrement la gestion des fuseaux de PostgreSQL et tout contrôler au niveau application.

-- Range query (last 7 days)
SELECT * FROM events WHERE created_at > EXTRACT(EPOCH FROM NOW())::bigint - 604800;

-- Convert to readable date
SELECT TO_TIMESTAMP(created_at) FROM events;

Pour la plupart des nouvelles applications PostgreSQL : utilisez TIMESTAMPTZ. La gestion des fuseaux est correcte, le type est lisible, et les fonctions datetime de PostgreSQL fonctionnent directement dessus.

MySQL : DATETIME vs TIMESTAMP vs INT

La terminologie de MySQL est source de confusion, car TIMESTAMP dans MySQL n’est pas la même chose que TIMESTAMPTZ dans PostgreSQL.

DATETIME — stocke la date et l’heure littérales que vous fournissez. Pas de conversion de fuseau. Similaire à TIMESTAMP WITHOUT TIME ZONE de PostgreSQL. Plage : 1000-01-01 à 9999-12-31.

TIMESTAMP — stocke en UTC en interne et convertit depuis/vers le fuseau de la connexion à la lecture/écriture. Similaire à TIMESTAMPTZ de PostgreSQL. Plage : 1970-01-01 00:00:01 UTC à 2038-01-19 03:14:07 UTC. La limite 2038 correspond au problème de l’an 2038 — le type TIMESTAMP de MySQL utilise un entier 32 bits en interne, ce qui provoque un dépassement.

INT ou BIGINT — gestion manuelle des timestamps Unix. Évite le problème 2038, car un BIGINT peut stocker des timestamps bien au-delà de 2038.

-- 2038 problem with TIMESTAMP column
-- This will fail or be stored incorrectly:
INSERT INTO events (created_at) VALUES (FROM_UNIXTIME(2147483648)); -- Jan 19, 2038 + 1 second

-- INT/BIGINT approach avoids this
INSERT INTO events (created_at) VALUES (2147483648); -- stores fine
SELECT FROM_UNIXTIME(created_at) FROM events; -- converts for display

Pour des applications MySQL dont les données doivent survivre au-delà de 2038 : utilisez DATETIME ou BIGINT plutôt que TIMESTAMP.

SQLite : tout est texte ou entier

SQLite n’a pas de types datetime natifs. Il stocke les dates sous forme :

de chaînes texte (format ISO 8601 : '2024-04-25T11:46:40')
d’entier (timestamp Unix)
de réel (numéro de jour julien)

Les fonctions de date SQLite fonctionnent avec les trois formats, mais en interne ce ne sont que du texte et des nombres. L’approche “entier” est naturelle dans SQLite et évite l’ambiguïté :

-- Store as integer
CREATE TABLE events (created_at INTEGER);
INSERT INTO events (created_at) VALUES (strftime('%s', 'now'));

-- Query last 7 days
SELECT * FROM events WHERE created_at > strftime('%s', 'now') - 604800;

-- Display as human-readable
SELECT datetime(created_at, 'unixepoch', 'localtime') FROM events;

Pour des applications SQLite, les timestamps Unix en entier sont souvent le choix le plus propre, précisément parce que SQLite les traite naturellement et qu’ils évitent le surcoût de parsing des chaînes datetime.

Requêtes courantes : entier vs syntaxe datetime

Opération	Entier Unix (PostgreSQL)	TIMESTAMPTZ (PostgreSQL)
Enregistrements des dernières 24 h	`WHERE ts > EXTRACT(EPOCH FROM NOW())::bigint - 86400`	`WHERE ts > NOW() - INTERVAL '24 hours'`
Enregistrements des 7 derniers jours	`WHERE ts > EXTRACT(EPOCH FROM NOW())::bigint - 604800`	`WHERE ts > NOW() - INTERVAL '7 days'`
Enregistrements d’aujourd’hui	`WHERE ts >= date_trunc('day', NOW())` (conversion nécessaire)	`WHERE ts >= date_trunc('day', NOW())`
Regrouper par jour	Complexe — convertir puis tronquer	`GROUP BY date_trunc('day', ts)`
Affichage lisible	`TO_TIMESTAMP(ts)`	`ts` (déjà lisible)

L’approche “entier” nécessite davantage de conversions explicites pour les opérations de troncature (grouper par jour/semaine/mois). Les colonnes datetime natives gèrent ces cas plus proprement en SQL.

Que choisir : guide de décision

Utilisez TIMESTAMPTZ (PostgreSQL) ou DATETIME (MySQL) si :

votre équipe écrit beaucoup de SQL directement (requêtes plus lisibles)
vous devez regrouper par jour/semaine/mois dans des requêtes
vous voulez utiliser les fonctions temps natives de la base sans conversion

Utilisez des timestamps Unix en entier si :

vous êtes sur MySQL et vous avez besoin de données au-delà de 2038
la couche applicative gère toute la logique de fuseau et vous voulez contourner la gestion de la base
vous utilisez SQLite
vous construisez une API où les timestamps seront envoyés à des frontends JavaScript (les entiers sont sans ambiguïté dès que vous documentez secondes vs millisecondes)
vous stockez des volumes très importants de timestamps et vous voulez un stockage compact (un INT 4 octets vs une représentation datetime variable)

Évitez de mélanger les approches dans une même application. Avoir certaines tables en datetime natif et d’autres en entiers crée une charge de conversion et un coût cognitif pour chaque développeur.

Quel que soit votre choix, documentez-le : ajoutez un commentaire à la définition de colonne indiquant s’il s’agit de secondes Unix UTC, de millisecondes Unix UTC, ou d’un datetime local. Utilisez le Convertisseur de timestamp Unix pour vérifier des valeurs spécifiques lors du débogage ou d’une migration de données.