MySQL DATETIME expliqué : guide complet des types de données date et heure

1. Qu’est‑ce que le DATETIME de MySQL ?

Le DATETIME de MySQL est un type de données conçu pour gérer simultanément les valeurs de date et d’heure. Gérer les dates et les heures dans une base de données est crucial pour de nombreuses applications, telles que les systèmes de journalisation ou de réservation. Le type DATETIME stocke à la fois la date et l’heure dans un seul champ et peut contenir une large plage de valeurs, de '1000-01-01 00:00:00' à '9999-12-31 23:59:59', et prend également en charge les fractions de seconde.

2. Vue d’ensemble des types de données date et heure de MySQL

2.1 Types de données pour gérer les dates et les heures

MySQL propose les types de données suivants pour gérer les dates et les heures :

• DATE : Un type de données pour gérer les dates (année, mois, jour). La plage va de '1000-01-01' à '9999-12-31' .
• TIME : Un type de données pour ne gérer que l’heure. La plage va de '-838:59:59' à '838:59:59' .
• DATETIME : Un type de données qui combine date et heure. La plage va de '1000-01-01 00:00:00' à '9999-12-31 23:59:59' .
• TIMESTAMP : Un type de données pour stocker les timestamps UNIX. La plage va de '1970-01-01 00:00:01' à '2038-01-19 03:14:07' .

2.2 Différences entre DATETIME et TIMESTAMP

Bien que DATETIME et TIMESTAMP soient similaires, ils présentent les différences clés suivantes :

• Fuseau horaire : DATETIME stocke une valeur fixe indépendante du fuseau horaire. En revanche, les valeurs TIMESTAMP sont converties en UTC lors du stockage, puis reconverties dans le fuseau horaire actuel du serveur lors de la récupération. Ainsi, DATETIME convient aux dates et heures qui ne sont pas affectées par les fuseaux horaires (par ex. les heures d’événements), tandis que TIMESTAMP est plus approprié pour les données liées au fuseau horaire du serveur, comme les enregistrements de logs.
• Format de stockage : DATETIME est stocké sous sa représentation exacte, alors que TIMESTAMP est stocké sous forme de timestamp UNIX. Par conséquent, la représentation horaire de TIMESTAMP dépend des paramètres de fuseau horaire du serveur.

3. Comment utiliser DATETIME dans MySQL

3.1 Création d’une colonne DATETIME

Pour créer une colonne de type DATETIME, utilisez la syntaxe SQL suivante :

CREATE TABLE sample_table (
    event_time DATETIME
);

Dans cet exemple, nous créons une colonne DATETIME nommée event_time dans une table appelée sample_table.

3.2 Insertion de valeurs DATETIME

Les valeurs DATETIME de MySQL peuvent être insérées sous différents formats. Le format de base est 'YYYY-MM-DD HH:MM:SS'. Par exemple :

INSERT INTO sample_table (event_time) VALUES ('2024-09-16 14:30:00');

D’autres formats autorisés incluent :

• 'YY-MM-DD HH:MM:SS' : Un format qui indique l’année avec deux chiffres.
• 'YYYYMMDDHHMMSS' : Un format qui indique les valeurs sans séparateurs.

Exemples :

INSERT INTO sample_table (event_time) VALUES ('24-09-16 14:30:00');
INSERT INTO sample_table (event_time) VALUES (20240916143000);

Les données insérées dans ces formats seront enregistrées correctement. Si l’année est spécifiée avec deux chiffres, '70-99' sera converti en 1970-1999, et '00-69' sera converti en 2000-2069.

3.3 Récupération des valeurs DATETIME

Lors de la récupération des valeurs DATETIME, MySQL les affiche au format par défaut 'YYYY-MM-DD HH:MM:SS'. Par exemple :

SELECT event_time FROM sample_table;

Cette requête affichera les valeurs de la colonne DATETIME de votre table au format standard.

4. Gestion des fractions de seconde

4.1 Précision du DATETIME

MySQL vous permet d’inclure des fractions de seconde dans les valeurs DATETIME. Vous pouvez spécifier la précision à l’aide de l’option fsp, qui autorise le stockage de fractions de seconde sur une plage de 0 à 6. Par exemple, pour créer une colonne avec trois décimales pour les fractions de seconde :

CREATE TABLE precise_times (
    event_time DATETIME(3)
);

Dans cet exemple, la colonne event_time peut stocker jusqu’à trois chiffres de fractions de seconde.

4.2 Insertion de valeurs avec fractions de seconde

Pour insérer une valeur DATETIME incluant des fractions de seconde, utilisez la syntaxe suivante :

INSERT INTO precise_times (event_time) VALUES ('2024-09-16 14:30:00.123');

Cette requête stocke avec précision les valeurs, y compris les fractions de seconde. La partie fractionnaire insérée est stockée sans troncature, et sa précision est conservée lors de la récupération.

5. Bonnes pratiques pour DATETIME

5.1 Quand utiliser DATETIME vs. TIMESTAMP

• Utilisez DATETIME lorsque : vous devez stocker des dates et heures fixes indépendantes des fuseaux horaires (par ex., heures de début d’événement, dates de réservation).
• Utilisez TIMESTAMP lorsque : vous devez stocker des données de date et heure liées au fuseau horaire du serveur (par ex., dates de création ou de mise à jour des données).

5.2 Gestion des fuseaux horaires

Étant donné que DATETIME n’a pas de notion de fuseau horaire, la gestion des fuseaux doit être prise en charge par l’application. En revanche, TIMESTAMP tient automatiquement compte du fuseau horaire du serveur lors du stockage et de la récupération des valeurs, ce qui le rend adapté aux opérations à travers différents fuseaux horaires dans le monde.

6. Erreurs courantes et comment les éviter

6.1 Dates nulles et valeurs invalides

Dans MySQL, tenter d’insérer une valeur DATETIME invalide entraîne le stockage d’une « date zéro » '0000-00-00 00:00:00'. Comme il s’agit généralement d’une date non valide, il est essentiel de valider les données d’entrée afin d’éviter l’insertion de valeurs invalides. Mettre en place une validation pour s’assurer que les données d’entrée respectent les plages et formats appropriés peut empêcher le stockage de dates zéro.

6.2 Mauvaise utilisation de la précision

Lors de la spécification de la précision des fractions de seconde, une précision incorrecte peut entraîner des résultats inattendus. Ne spécifiez la précision des fractions de seconde que lorsque cela est nécessaire, et définissez soigneusement la valeur fsp. Par exemple, si votre application n’a pas besoin d’une précision sous-seconde, il n’est pas nécessaire de définir des fractions de seconde pour votre colonne DATETIME.

7. Conclusion

Cet article fournit une explication détaillée du type de données DATETIME de MySQL. DATETIME est un type de données très utile pour gérer simultanément la date et l’heure, adapté au stockage de valeurs indépendantes des fuseaux horaires. En comprenant les différences entre DATETIME et TIMESTAMP, la gestion des fuseaux horaires et l’utilisation des fractions de seconde, vous pouvez gérer efficacement les données de date et d’heure dans votre base de données. De plus, la connaissance des erreurs courantes et des stratégies pour les éviter contribuera à maintenir la cohérence et la fiabilité des données.

8. Foire aux questions (FAQ)

Q1 : Quelles sont les principales différences entre DATETIME et TIMESTAMP ?

DATETIME stocke une date et une heure fixes, indépendantes des fuseaux horaires. Par exemple, il convient pour stocker des dates de réservation ou des heures d’événement qui restent cohérentes dans tous les fuseaux. En revanche, TIMESTAMP est stocké en UTC et converti dans le fuseau horaire du serveur lors de la récupération. Il convient aux données sensibles au temps, comme les enregistrements de journal, qui dépendent du fuseau horaire du serveur.

Q2 : Comment puis‑je stocker des fractions de seconde avec DATETIME ?

Lors de la création d’une colonne DATETIME, vous pouvez définir la précision des fractions de seconde en spécifiant la valeur fsp. Par exemple, DATETIME(3) permet de stocker des fractions de seconde jusqu’à trois décimales. Lors de l’insertion, utilisez une valeur incluant les fractions de seconde, qui sera stockée dans le format approprié.

Q3 : Dois‑je utiliser DATETIME ou TIMESTAMP ?

Cela dépend de votre cas d’utilisation. Utilisez DATETIME si vous souhaitez stocker une date et une heure fixes. En revanche, utilisez TIMESTAMP pour les données de date et d’heure affectées par le fuseau horaire du serveur, comme les dates de création ou de mise à jour des données. TIMESTAMP est approprié lorsque la conversion automatique des fuseaux horaires est requise, notamment pour des opérations à travers différents fuseaux.