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
MongoDB stores dates natively as the BSON Date type — a signed 64-bit integer of milliseconds since the Unix epoch. The driver maps it to a Date object in JavaScript, datetime in Python, ISODate in the mongo shell.
- BSON Date: 8 bytes, signed 64-bit, milliseconds since 1970 UTC
- Indexes natively — range queries on Date are fast and idiomatic
- Use ISODate("2024-01-01T00:00:00Z") in the shell to create comparable values
- For multi-precision needs, use Decimal128 or a sub-document with seconds + nanos; Date itself is millisecond precision
- TTL indexes operate on Date fields specifically — integer epoch fields will not work with expireAfterSeconds
DynamoDB: epoch attributes and TTL
DynamoDB has no dedicated date type. Two patterns dominate: ISO 8601 strings for human readability, or epoch numbers for compactness and TTL support.
- TTL requires the attribute to be a Number representing Unix seconds (not milliseconds)
- Strings store ISO 8601 with full timezone info but cost more bytes and sort lexicographically (so include the Z suffix consistently)
- Range queries on a sort-key epoch number are extremely cheap and align with DynamoDB’s query model
- Avoid mixing seconds and milliseconds across tables — DynamoDB will not protect you from the unit drift
- DynamoDB Streams emit an ApproximateCreationDateTime in ISO 8601 regardless of how you stored the original value
FAQ
- Should I store UTC or local time in a database?
- Store UTC for event timestamps and convert to local time when displaying. Store a timezone identifier separately when the user's local wall-clock intent matters, such as recurring meetings or business hours.
- Is BIGINT better than TIMESTAMP?
- Not generally. BIGINT is useful for numeric epoch pipelines, but native datetime types are easier for SQL date arithmetic, readable debugging, and timezone-aware output.
- Should MySQL use TIMESTAMP or DATETIME?
- For new application tables, DATETIME with UTC values is often safer because it avoids the 2038 range limit and does not silently depend on session timezone conversion.
- Should I store timestamps as UTC or with a timezone?
- Store the instant in UTC (TIMESTAMPTZ in PostgreSQL, or DATETIME with UTC values in MySQL) and convert to local time on display. Keep a separate IANA timezone column only when you must reconstruct a user's local wall-clock intent, such as recurring meetings.