MySQL DATETIME expliqué : type de données, plage et bonnes pratiques

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

Le DATETIME de MySQL est un type de données utilisé pour stocker à la fois la date et l’heure dans un même champ. Gérer les valeurs de date et d’heure dans une base de données est essentiel pour de nombreuses applications, comme les systèmes de journalisation et de réservation. Le type DATETIME stocke une date et une heure ensemble et prend en charge une large gamme de valeurs. Son intervalle va de '1000-01-01 00:00:00' à '9999-12-31 23:59:59', et il accepte également 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 travailler avec les dates et les heures :

• DATE : stocke une date (année, mois, jour). L’intervalle est '1000-01-01' à '9999-12-31'.
• TIME : stocke uniquement l’heure. L’intervalle est '-838:59:59' à '838:59:59'.
• DATETIME : stocke à la fois la date et l’heure. L’intervalle est '1000-01-01 00:00:00' à '9999-12-31 23:59:59'.
• TIMESTAMP : stocke un horodatage UNIX. L’intervalle est '1970-01-01 00:00:01' à '2038-01-19 03:14:07'.

2.2 Différences entre DATETIME et TIMESTAMP

DATETIME et TIMESTAMP sont similaires, mais présentent les différences suivantes :

• Fuseau horaire : DATETIME stocke une valeur fixe qui ne dépend pas des fuseaux horaires. En revanche, TIMESTAMP est converti en UTC lors du stockage et reconverti dans le fuseau horaire actuel du serveur lors de la lecture. À cause de cela, DATETIME convient aux valeurs de date/heure indépendantes du fuseau (par exemple, les heures d’événements), tandis que TIMESTAMP convient aux données liées au fuseau horaire du serveur, comme les journaux.
• Format de stockage : DATETIME est stocké tel quel, alors que TIMESTAMP est stocké sous forme d’horodatage UNIX. Par conséquent, les valeurs TIMESTAMP sont affectées par les paramètres de fuseau horaire du serveur lorsqu’on les représente.

3. Comment utiliser DATETIME dans MySQL

3.1 Créer 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, une colonne DATETIME nommée event_time est créée dans une table appelée sample_table.

3.2 Insérer des 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');

Les formats suivants sont également autorisés :

• 'YY-MM-DD HH:MM:SS' : un format qui utilise une année à deux chiffres.
• 'YYYYMMDDHHMMSS' : un format sans séparateurs.

Exemple :

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 avec ces formats seront stockées correctement. Si l’année est spécifiée avec deux chiffres, '70-99' est converti en 1970-1999 et '00-69' en 2000-2069.

3.3 Récupérer 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 affiche les valeurs de la colonne DATETIME en utilisant le format standard.

4. Travailler avec les fractions de seconde

4.1 Précision du DATETIME

Dans MySQL, les valeurs DATETIME peuvent inclure des fractions de seconde. Vous pouvez spécifier la précision avec l’option fsp, qui permet de stocker des fractions de seconde de 0 à 6 chiffres. Par exemple, pour créer une colonne avec 3 chiffres de fractions de seconde :

CREATE TABLE precise_times (
    event_time DATETIME(3)
);

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

4.2 Insérer des valeurs avec des fractions de seconde

Pour insérer une valeur DATETIME incluant des fractions de seconde, utilisez ce qui suit :

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

Cette requête stocke la valeur incluant les fractions de seconde avec précision. La partie fractionnaire est stockée sans être tronquée, et la précision est conservée lors de la récupération de la valeur.

5. Bonnes pratiques pour DATETIME

5.1 Choisir entre DATETIME et TIMESTAMP

• Quand utiliser DATETIME : Pour des valeurs de date/heure fixes qui ne dépendent pas des fuseaux horaires (par exemple, les heures de début d’événements ou les dates de réservation).
• Quand utiliser TIMESTAMP : Pour des données de date/heure liées au fuseau horaire du serveur (par exemple, les horodatages de création ou de mise à jour d’enregistrements).

5.2 Gestion des fuseaux horaires

Comme DATETIME ne stocke pas d’information de fuseau horaire, votre application doit gérer les fuseaux horaires séparément. En revanche, TIMESTAMP prend automatiquement en compte le fuseau horaire du serveur lors du stockage et de la récupération des valeurs, ce qui le rend adapté aux systèmes fonctionnant à travers différents fuseaux horaires dans le monde.

6. Erreurs courantes et comment les éviter

6.1 Dates nulles et valeurs invalides

Dans MySQL, si vous essayez d’insérer une valeur DATETIME invalide, une date zéro comme '0000-00-00 00:00:00' peut être stockée. Comme il s’agit généralement d’une date non valide, vous devez valider les données d’entrée pour empêcher l’insertion de valeurs invalides. Mettre en place une validation qui garantit que l’entrée respecte la plage et le format corrects peut aider à éviter 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, utiliser une mauvaise précision peut produire des résultats inattendus. N’ajustez la précision des fractions de seconde que lorsque cela est nécessaire, et choisissez la valeur fsp avec soin. Par exemple, si votre application n’exige pas de précision sous-seconde, vous n’avez pas besoin d’ajouter des fractions de seconde à une colonne DATETIME.

7. Résumé

Dans cet article, nous avons expliqué en détail le type DATETIME de MySQL. DATETIME est un type de données très utile pour stocker à la fois la date et l’heure, et il convient lorsque vous devez stocker des valeurs qui ne doivent pas être affectées par les fuseaux horaires. En comprenant les différences entre DATETIME et TIMESTAMP, le fonctionnement des fuseaux horaires et l’utilisation des fractions de seconde, vous pouvez gérer les données de date/heure plus efficacement dans votre base de données. De plus, connaître les erreurs courantes et comment les éviter aide à maintenir la cohérence et la fiabilité des données.

8. Foire aux questions (FAQ)

Q1 : Quelle est la principale différence entre DATETIME et TIMESTAMP ?

DATETIME stocke une date et une heure fixes qui ne dépendent pas des fuseaux horaires. Par exemple, il convient pour stocker des dates de réservation ou des heures d’événement qui doivent rester identiques quel que soit le fuseau horaire. 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 de date/heure qui dépendent du fuseau horaire du serveur, comme les journaux.

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

Vous pouvez définir la précision des fractions de seconde en spécifiant une valeur fsp lors de la création de la colonne DATETIME. Par exemple, DATETIME(3) stocke les fractions de seconde jusqu’à 3 chiffres. Utilisez une valeur correctement formatée incluant les fractions de seconde lors de l’insertion, et elle sera stockée correctement.

Q3 : Dois‑je utiliser DATETIME ou TIMESTAMP ?

Cela dépend de votre cas d’utilisation. Utilisez DATETIME lorsque vous souhaitez stocker une date et une heure fixes. Utilisez TIMESTAMP pour les données de date/heure qui sont affectées par le fuseau horaire du serveur, comme les heures de création ou de mise à jour d’enregistrements. Comme TIMESTAMP effectue une conversion automatique des fuseaux horaires, il convient lorsque votre système doit fonctionner à travers différents fuseaux horaires.