Stocker des timestamps en base : DATETIME vs INT vs BIGINT

Trois façons de stocker un timestamp

La plupart des bases offrent au moins trois options : un type datetime natif (TIMESTAMP, DATETIME, TIMESTAMPTZ), un entier simple (INT ou BIGINT) ou une chaîne (VARCHAR). Chacun a des compromis différents en taille de stockage, ergonomie de requête, gestion des fuseaux et pérennité. Pour la plupart des bases de produit, une colonne datetime native est le meilleur défaut car la base peut comparer, indexer, tronquer, regrouper et formater la valeur comme du temps plutôt que comme un nombre anonyme.

• Type datetime natif — le meilleur pour l'arithmétique de dates, la conversion de fuseau et la lisibilité
• Entier BIGINT — bon pour les insertions à haut débit et les requêtes numériques de plage simples
• Chaîne VARCHAR — presque toujours faux : la comparaison de chaînes de dates ne marche qu'avec le format ISO 8601 strict
• Entier INT — à éviter pour les timestamps futurs sauf vérification complète de la limite de l'an 2038

MySQL : TIMESTAMP vs DATETIME vs INT

MySQL a deux types date-heure qui se ressemblent mais se comportent très différemment — et l'un d'eux a une date d'expiration rigide. TIMESTAMP est pratique quand on veut une conversion automatique entre UTC et le fuseau de session, mais sa plage historique 32 bits le rend risqué pour des données de produit tournées vers l'avenir. DATETIME stocke la date et l'heure littérales fournies, ce qui est généralement plus clair quand l'application standardise sur UTC avant d'écrire.

• TIMESTAMP : stocké en interne en secondes Unix 32 bits — limité de 1970-01-01 à 2038-01-19
• TIMESTAMP : auto-convertit entre UTC et le fuseau de session à l'insertion/lecture
• DATETIME : stocke la date-heure littérale, sans fuseau. Plage 1000-01-01 à 9999-12-31. Non affecté par le Y2038.
• DATETIME : ne convertit pas les fuseaux — vous contrôlez UTC au niveau application
• Recommandation : utilisez DATETIME avec des valeurs UTC explicites pour les nouvelles tables afin d'éviter la limite de 2038

PostgreSQL : TIMESTAMPTZ est le bon choix

Le TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ) de PostgreSQL stocke les timestamps en microsecondes UTC en interne et les convertit vers le fuseau de session en sortie. C'est l'option la plus sûre et la plus correcte pour la plupart des cas car elle représente un instant réel. Le nom peut induire en erreur : TIMESTAMPTZ ne stocke pas l'étiquette de fuseau d'origine comme America/New_York. Il stocke l'instant, puis l'affiche selon le fuseau de session courant.

• TIMESTAMPTZ : stocke UTC, convertit vers le fuseau de session en sortie — portable et sûr face à l'heure d'été
• TIMESTAMP (sans fuseau) : stocke la valeur littérale sans conversion — à utiliser seulement pour des données sans fuseau
• EXTRACT(EPOCH FROM col) : renvoie les secondes Unix en float depuis toute colonne TIMESTAMP
• TO_TIMESTAMP(epoch) : convertit les secondes Unix en un TIMESTAMPTZ

Indexation et performance des requêtes

Pour les tables d'application normales, la différence de performance entre des colonnes datetime natives et des colonnes epoch BIGINT est rarement le facteur décisif. La forme de la requête, la conception de l'index, le partitionnement et le nombre de lignes comptent davantage. Choisissez d'abord le type qui garde le sens correct, puis indexez-le pour les requêtes de plage que votre application exécute réellement.

• Les trois types supportent les index B-tree et des requêtes de plage efficaces
• Les entiers BIGINT sont marginalement plus rapides pour les scans d'égalité et de plage sur les tables à très gros volume
• Les types datetime natifs permettent des requêtes indexées par partie de date : WHERE created_at::date = '2024-01-01'
• Les timestamps VARCHAR sont les pires pour la performance — la comparaison de chaînes ne comprend pas les dates

Quand le stockage epoch en BIGINT a du sens

BIGINT est raisonnable quand les données sont de type événement, avec beaucoup d'insertions, et déjà produites en temps Unix par un autre système. Les pipelines d'analytics, les flux de télémétrie, les files d'attente et les protocoles binaires compacts utilisent souvent les millisecondes epoch car les valeurs numériques sont rapides à comparer et neutres au langage. Le compromis est la lisibilité : les humains ont besoin d'un convertisseur et l'arithmétique de dates SQL devient plus verbeuse.

• Utilisez BIGINT pour les millisecondes Unix si des clients JavaScript produisent les événements directement
• Utilisez BIGINT pour les secondes Unix si le système source est de style Unix et que la précision à la seconde suffit
• Documentez l'unité dans le nom de colonne : created_at_ms est plus clair que created_at_epoch
• Ajoutez une colonne datetime générée si les analystes ont besoin de requêtes SQL lisibles
• Évitez INT pour les timestamps modernes tournés vers l'avenir à cause des limites de plage 32 bits

Modèles de schéma recommandés

Pour la plupart des applications web, stockez un instant en UTC et stockez le fuseau préféré de l'utilisateur séparément seulement quand vous devez reconstruire l'intention d'horloge locale. Une réunion prévue à 9h00 America/New_York est différente d'un log d'événement créé à un instant UTC précis ; modélisez ces cas différemment.

• Logs d'événements : created_at TIMESTAMPTZ en PostgreSQL, ou created_at DATETIME en UTC pour MySQL
• Ingestion d'événements JavaScript : created_at_ms BIGINT plus une documentation API claire
• Planifications locales récurrentes : local_date, local_time et timezone_id, puis calculez le prochain instant
• Timestamps d'expiration : expires_at en datetime natif ou expires_at_seconds avec des secondes Unix explicites
• Tables d'audit : gardez created_at et updated_at en colonnes datetime natives pour un débogage lisible

FAQ sur les timestamps en base de données

Dois-je stocker UTC ou l'heure locale dans une base ?

Stockez UTC pour les timestamps d'événements et convertissez en heure locale à l'affichage. Stockez un identifiant de fuseau séparément quand l'intention d'horloge locale de l'utilisateur compte, comme les réunions récurrentes ou les heures d'ouverture.

BIGINT est-il meilleur que TIMESTAMP ?

Pas en général. BIGINT est utile pour les pipelines numériques d'epoch, mais les types datetime natifs sont plus simples pour l'arithmétique de dates SQL, le débogage lisible et la sortie tenant compte du fuseau.

MySQL doit-il utiliser TIMESTAMP ou DATETIME ?

Pour les nouvelles tables d'application, DATETIME avec des valeurs UTC est souvent plus sûr car il évite la limite de plage de 2038 et ne dépend pas silencieusement de la conversion de fuseau de session.

Dois-je stocker les timestamps en UTC ou avec un fuseau ?

Stockez l'instant en UTC (TIMESTAMPTZ en PostgreSQL, ou DATETIME avec des valeurs UTC en MySQL) et convertissez en heure locale à l'affichage. Gardez une colonne de fuseau IANA séparée seulement quand vous devez reconstruire l'intention d'horloge locale de l'utilisateur, comme les réunions récurrentes.