TIMESTAMP MySQL expliqué : fuseaux horaires, différences avec DATETIME et le problème de 2038

1. Qu’est‑ce que le TIMESTAMP MySQL ?

Le type de données TIMESTAMP dans MySQL est conçu pour stocker un point précis dans le temps en UTC (Temps Universel Coordonné) et gérer automatiquement la conversion de fuseau horaire lors de l’enregistrement et de la récupération des données. Ce type de données peut gérer des dates et heures allant du 1 janvier 1970 au 19 janvier 2038. Lors de l’enregistrement des données dans la base, TIMESTAMP utilise le fuseau horaire actuel, et lors de la récupération, il est automatiquement converti en fonction du fuseau horaire du système.

Différences entre TIMESTAMP et DATETIME

Le type de données DATETIME est souvent comparé à TIMESTAMP. DATETIME stocke les valeurs de date et d’heure « telles quelles », de sorte que les données enregistrées ne sont pas affectées par les fuseaux horaires. En revanche, TIMESTAMP est converti en UTC lors du stockage et reconverti dans le fuseau horaire du système lors de la lecture, ce qui aide à éviter les décalages horaires entre différents environnements.

Par exemple, TIMESTAMP est particulièrement utile lors de migrations de systèmes ou lorsqu’on travaille avec des bases de données réparties sur plusieurs fuseaux horaires. DATETIME couvre une plage plus large — de l’an 1000 à 9999 — et est donc souvent utilisé pour contourner le problème de l’an 2038.

Exemple d’utilisation du TIMESTAMP

Vous pouvez créer une table en utilisant TIMESTAMP comme suit.

CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Dans cet exemple, la colonne event_time stocke automatiquement l’heure actuelle lorsqu’un enregistrement est inséré, et écrase cette valeur à chaque mise à jour de l’enregistrement.

2. Utilisation de base du TIMESTAMP

Lorsqu’on utilise TIMESTAMP dans MySQL, il est important de comprendre les manières de base d’insérer et de récupérer des valeurs. Voici plusieurs approches courantes pour travailler avec des données TIMESTAMP.

Insérer une date et une heure

Lors de l’insertion de données dans une colonne TIMESTAMP, on spécifie généralement la date et l’heure sous forme de chaîne. La date est représentée sous la forme « YYYY‑MM‑DD » et l’heure sous la forme « hh:mm:ss ».

INSERT INTO events (event_time) VALUES ('2023-10-01 12:30:00');

Cette instruction SQL insère le 1 octobre 2023 à 12 :30 :00 dans la colonne event_time.

Insérer l’heure actuelle

En utilisant la fonction NOW() de MySQL, vous pouvez obtenir facilement la date et l’heure actuelles. Cette fonction renvoie la date et l’heure courantes selon le fuseau horaire du système, et vous pouvez l’insérer directement dans une colonne TIMESTAMP.

INSERT INTO events (event_time) VALUES (NOW());

Dans cet exemple, l’heure actuelle au moment de l’exécution de la requête SQL est insérée automatiquement.

Utiliser la fonction de mise à jour automatique

Si vous spécifiez ON UPDATE CURRENT_TIMESTAMP pour une colonne TIMESTAMP, l’heure de mise à jour est enregistrée automatiquement à chaque modification de l’enregistrement.

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Dans cette table, order_time est fixé à l’heure actuelle lors de la création de l’enregistrement, et il est mis à jour à chaque modification de l’enregistrement.

3. Travailler avec le TIMESTAMP et les fuseaux horaires

L’une des principales fonctionnalités du TIMESTAMP est la gestion des fuseaux horaires. Les données stockées sont toujours converties en UTC, et lorsqu’on les récupère depuis la base, elles sont reconverties pour correspondre au fuseau horaire du système.

Comment vérifier le paramètre du fuseau horaire

Dans MySQL, vous pouvez définir le fuseau horaire au niveau du serveur ou de la session. Vous pouvez vérifier le paramètre du fuseau horaire à l’aide de la commande SHOW VARIABLES.

SHOW VARIABLES LIKE 'time_zone';

Cette commande renvoie le fuseau horaire actuellement configuré pour la base de données. Pour modifier le fuseau horaire, utilisez l’instruction suivante.

SET time_zone = '+09:00';

Différences de fuseau horaire entre TIMESTAMP et DATETIME

Le type DATETIME stocke la date et l’heure sans tenir compte des fuseaux horaires, tandis que le type TIMESTAMP est converti en UTC lors de l’enregistrement. Ainsi, dans des environnements où plusieurs fuseaux horaires coexistent, TIMESTAMP est souvent le meilleur choix.

4. Le problème de l’an 2038 et son impact

Le problème de l’an 2038 est causé par la limitation du type TIMESTAMP sur les systèmes 32 bits. Le type TIMESTAMP de MySQL est basé sur le nombre de secondes écoulées depuis le 00 h 00 m 00 s UTC du 1er janvier 1970. Lorsqu’il dépasse le 19 janvier 2038 à 03 h 14 m 07 s UTC, cette valeur déborde.

Comment éviter le problème de l’an 2038

Pour éviter ce problème, il est recommandé d’utiliser un système 64 bits ou le type DATETIME à plus grande portée. DATETIME peut gérer des dates et heures de l’an 1000 à 9999, il peut donc être utilisé en toute sécurité au‑delà de 2038.

Vous pouvez également contourner ce problème en mettant à jour votre système. Les systèmes 64 bits ne présentent pas la limitation de l’an 2038, il est donc important d’envisager la mise à niveau de votre base de données et de vos applications.

5. Cas d’utilisation pratiques du type TIMESTAMP

Le type MySQL TIMESTAMP n’est pas seulement utilisé pour stocker des valeurs de date et d’heure de base, il prend également en charge de nombreux modèles pratiques tels que l’insertion ou la mise à jour automatiques de l’heure actuelle. Voici quelques cas d’utilisation avancés courants.

Insertion automatique de l’heure actuelle

Lors de la définition d’une colonne TIMESTAMP, vous pouvez définir CURRENT_TIMESTAMP comme valeur par défaut afin que la date et l’heure actuelles soient insérées automatiquement chaque fois qu’un nouvel enregistrement est créé. Par exemple, pour créer une table qui enregistre automatiquement le moment où une commande est passée, vous pouvez procéder comme suit.

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Enregistrement automatique de l’heure de mise à jour

En spécifiant ON UPDATE CURRENT_TIMESTAMP, l’heure de mise à jour est enregistrée automatiquement à chaque modification d’un enregistrement. Cela facilite la gestion automatique de l’historique des mises à jour.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Utilisation de plusieurs colonnes TIMESTAMP

Dans MySQL, vous pouvez inclure plusieurs colonnes TIMESTAMP dans une table, mais par défaut une seule colonne peut avoir CURRENT_TIMESTAMP comme valeur par défaut. Si vous souhaitez gérer plusieurs horodatages automatiquement, définissez explicitement les valeurs des autres colonnes ou utilisez le type DATETIME.

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

6. Remarques importantes lors de l’utilisation du type TIMESTAMP

Lors de l’utilisation du type TIMESTAMP, plusieurs points importants doivent être compris. Les connaître permet d’éviter des incohérences et des erreurs inattendues.

Contraintes NULL et valeurs par défaut

Par défaut, une contrainte NOT NULL est appliquée aux colonnes TIMESTAMP. Autrement dit, si vous voulez autoriser les valeurs NULL, vous devez spécifier explicitement DEFAULT NULL.

CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    log_time TIMESTAMP DEFAULT NULL
);

Vous pouvez également spécifier DEFAULT 0 pour définir, par défaut, une valeur datetime invalide 0000-00-00 00:00:00. Cependant, cela n’est pas recommandé. En mode SQL strict de MySQL, cette datetime invalide peut provoquer des erreurs.

Le problème de 0000-00-00 00:00:00

Certaines versions de MySQL acceptent 0000-00-00 00:00:00 comme valeur datetime invalide, mais cela peut entraîner des problèmes opérationnels dans les systèmes réels. En particulier, les systèmes qui privilégient l’intégrité des données devraient éviter de telles valeurs invalides. Il est préférable d’utiliser NULL ou une valeur par défaut appropriée.

CREATE TABLE sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    start_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    end_time TIMESTAMP NULL
);

Impact du fuseau horaire du système

Comme le type TIMESTAMP est stocké après conversion en UTC, il faut être prudent lors de la migration de bases de données entre différents fuseaux horaires. Si le réglage du fuseau horaire du serveur change, les heures récupérées peuvent devenir des valeurs non souhaitées. Il est donc essentiel de gérer les fuseaux horaires avec précision.

SET time_zone = 'Asia/Tokyo';

This command sets the database time zone to Tokyo and ensures accurate management of conversions from UTC.

7. Résumé et recommandations

Le type TIMESTAMP est un outil puissant pour gérer efficacement les dates et heures dans MySQL. En particulier, la conversion automatique avec les fuseaux horaires et l’enregistrement automatique de l’heure lors de la création/mise à jour sont très pratiques. Cependant, il est important de comprendre les limites et les mises en garde, telles que le problème de l’an 2038 et la façon dont les valeurs NULL sont gérées.

Quand utiliser TIMESTAMP

• Lorsque vous avez besoin d’un comportement de mise à jour automatique, TIMESTAMP est idéal—surtout si vous souhaitez enregistrer automatiquement un horodatage chaque fois qu’un enregistrement est mis à jour.
• Dans les systèmes qui doivent prendre en compte les fuseaux horaires, le comportement de conversion basé sur l’UTC de TIMESTAMP est utile.
• En revanche, si vous avez besoin d’une plage de dates pérenne ou devez gérer des dates en dehors de la plage prise en charge (après 2038), envisagez d’utiliser DATETIME.

Enfin, choisissez entre TIMESTAMP et DATETIME en fonction des exigences de votre système afin d’assurer l’intégrité et la maintenabilité des données.

8. Questions fréquemment posées (FAQ)

Les questions et problèmes liés au TIMESTAMP de MySQL sont courants parmi les développeurs, c’est pourquoi cette section résume les questions fréquemment posées. Ces FAQ offrent des conseils utiles et des solutions pour gérer correctement le TIMESTAMP.

Comment choisir entre TIMESTAMP et DATETIME ?

TIMESTAMP convertit automatiquement les valeurs en fonction des fuseaux horaires en utilisant l’UTC, il convient donc aux applications et systèmes qui doivent prendre en compte plusieurs fuseaux horaires. Il prend également en charge l’enregistrement automatique des valeurs de date/heure lors de la création ou de la mise à jour des enregistrements. En revanche, DATETIME stocke les valeurs « telles quelles », il est donc préférable lorsque vous avez besoin d’une gestion cohérente des dates et heures sans conversion de fuseau horaire.

Est‑il vrai que TIMESTAMP ne peut pas être utilisé après 2038 ?

Oui. Le problème de l’an 2038 s’applique au type TIMESTAMP 32 bits. Comme il est basé sur le nombre de secondes écoulées depuis le 1er janvier 1970, il ne peut pas représenter les dates après le 19 janvier 2038. Pour éviter cela, il est recommandé de migrer vers un système 64 bits ou d’utiliser le type DATETIME.

Comment autoriser les valeurs NULL dans une colonne TIMESTAMP ?

Pour autoriser les valeurs NULL dans une colonne TIMESTAMP, vous devez spécifier explicitement DEFAULT NULL comme indiqué ci‑dessous.

CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    log_time TIMESTAMP DEFAULT NULL
);

Avec ce réglage, si vous insérez un enregistrement sans spécifier de date/heure, une valeur NULL est stockée.

Si je modifie le réglage du fuseau horaire, cela affectera‑t‑il les données TIMESTAMP existantes ?

Comme les valeurs TIMESTAMP sont converties en UTC lors du stockage, modifier le réglage du fuseau horaire affecte la façon dont les données sont affichées lors de la récupération. Les données sous‑jacentes restent stockées en UTC, mais elles seront converties en fonction du nouveau fuseau horaire, ce qui modifie les valeurs de temps récupérées. Pour maintenir la cohérence des données, il est important de standardiser les réglages de fuseau horaire dans tout le système.

Si j’utilise CURRENT_TIMESTAMP, puis‑je toujours insérer une date/heure spécifique ?

CURRENT_TIMESTAMP insère automatiquement l’heure actuelle lors de l’insertion de l’enregistrement, mais vous pouvez toujours insérer explicitement une date/heure spécifique en utilisant NOW() ou une chaîne littérale.

INSERT INTO events (event_time) VALUES ('2023-10-01 12:30:00');

De cette manière, vous pouvez insérer manuellement des dates/heure même en utilisant CURRENT_TIMESTAMP.