Comparaison et manipulation des dates MySQL : guide complet avec exemples et conseils de performance

1. Introduction

La gestion des dates dans MySQL est l’un des aspects les plus importants des opérations de base de données. Par exemple, lors de l’agrégation des données de ventes par date ou de la recherche d’enregistrements sur une période spécifique, la comparaison de dates devient essentielle.

Cet article explique tout, des bases de la manipulation et de la comparaison des dates dans MySQL aux cas d’utilisation avancés et aux techniques d’optimisation des performances. Il est conçu pour être utile aux utilisateurs de tous niveaux, des débutants aux développeurs intermédiaires.

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

Types de données de date et leurs caractéristiques

MySQL propose les trois principaux types de données de date suivants. Vous trouverez ci‑dessous une brève explication de chacun.

1. DATE

• Valeur stockée : Année-Mois-Jour ( YYYY-MM-DD )
• Caractéristiques : ne comprend pas d’information d’heure, ce qui le rend adapté à la gestion de dates simples.
• Cas d’utilisation : anniversaires, échéances.

1. DATETIME

• Valeur stockée : Année-Mois-Jour et heure ( YYYY-MM-DD HH:MM:SS )
• Caractéristiques : comprend l’information d’heure, ce qui le rend adapté à l’enregistrement de timestamps précis.
• Cas d’utilisation : timestamps de création, timestamps de dernière mise à jour.

1. TIMESTAMP

• Valeur stockée : Année-Mois-Jour et heure ( YYYY-MM-DD HH:MM:SS )
• Caractéristiques : dépend du fuseau horaire, ce qui le rend utile pour gérer les dates et heures à travers différentes régions.
• Cas d’utilisation : données de journal, enregistrements de transactions.

Comment choisir le type de données approprié

• Si l’heure n’est pas requise, choisissez DATE.
• Si l’heure est requise, choisissez DATETIME ou TIMESTAMP selon les exigences de fuseau horaire de votre application.

Exemple de requête

Vous trouverez ci‑dessous un exemple utilisant chaque type de données.

CREATE TABLE events (
    event_id INT AUTO_INCREMENT PRIMARY KEY,
    event_date DATE,
    event_datetime DATETIME,
    event_timestamp TIMESTAMP
);

3. Comment comparer les dates

Utilisation des opérateurs de comparaison de base

Dans MySQL, les opérateurs suivants sont utilisés pour la comparaison de dates.

1. = (Égal)

• Récupère les enregistrements qui correspondent à la date spécifiée.

  SELECT * FROM events WHERE event_date = '2025-01-01';
  

2. > / < (Supérieur à / Inférieur à)

• Recherche les enregistrements avant ou après la date spécifiée.

  SELECT * FROM events WHERE event_date > '2025-01-01';
  

3. <= / >= (Inférieur ou égal / Supérieur ou égal)

• Recherche dans une plage qui inclut la date spécifiée.

  SELECT * FROM events WHERE event_date <= '2025-01-10';
  

Requêtes de plage avec BETWEEN

L’opérateur BETWEEN vous permet de spécifier facilement une plage de dates.

SELECT * FROM events 
WHERE event_date BETWEEN '2025-01-01' AND '2025-01-31';

Note importante :

• BETWEEN inclut à la fois les valeurs de début et de fin, assurez‑vous qu’aucune donnée requise n’est exclue involontairement de votre plage.

4. Calcul des différences de dates

Utilisation de la fonction DATEDIFF

La fonction DATEDIFF() calcule la différence (en jours) entre deux dates.

SELECT DATEDIFF('2025-01-10', '2025-01-01') AS days_difference;

Résultat :

• 9 jours

Utilisation de la fonction TIMESTAMPDIFF

La fonction TIMESTAMPDIFF() vous permet de calculer la différence dans des unités spécifiques telles que les années, les mois, les jours ou les heures.

SELECT TIMESTAMPDIFF(MONTH, '2025-01-01', '2025-12-31') AS months_difference;

Résultat :

• 11 mois

5. Addition et soustraction de dates

Manipulation de dates avec la clause INTERVAL

Dans MySQL, vous pouvez facilement ajouter ou soustraire du temps à une date en utilisant la clause INTERVAL. Cela vous permet de créer des requêtes qui récupèrent des dates avant ou après une période spécifique.

Ajout à une date

Exemple d’ajout de temps à une date en utilisant INTERVAL :

SELECT DATE_ADD('2025-01-01', INTERVAL 7 DAY) AS plus_seven_days;

Résultat :
2025-01-08

Soustraction d’une date

Vous pouvez soustraire du temps d’une date de manière similaire en utilisant INTERVAL :

SELECT DATE_SUB('2025-01-01', INTERVAL 1 MONTH) AS minus_one_month;

Résultat :
2024-12-01

Cas d’utilisation : Système de rappel

Voici un exemple de requête qui récupère les événements survenus exactement il y a sept jours, pouvant être utilisé pour envoyer des notifications de rappel automatisées.

SELECT event_name, event_date 
FROM events 
WHERE event_date = DATE_SUB(CURDATE(), INTERVAL 7 DAY);

Calculs basés sur la date actuelle

• CURDATE() : Renvoie la date actuelle (YYYY-MM-DD).
• NOW() : Renvoie la date et l’heure actuelles (YYYY-MM-DD HH:MM:SS).

Exemple : Calculer la date une semaine à partir d’aujourd’hui.

SELECT DATE_ADD(CURDATE(), INTERVAL 7 DAY) AS next_week;

6. Exemples de requêtes pratiques

Récupérer les enregistrements pour une date spécifique

Récupérer les événements qui correspondent à la date spécifiée.

SELECT * 
FROM events 
WHERE event_date = '2025-01-01';

Récupérer des données dans une plage de dates

Exemple de recherche d’événements sur une période d’une semaine.

SELECT * 
FROM events 
WHERE event_date BETWEEN '2025-01-01' AND '2025-01-07';

Agréger les données par date

Cette requête compte le nombre d’événements enregistrés pour chaque mois.

SELECT MONTH(event_date) AS event_month, COUNT(*) AS total_events 
FROM events 
GROUP BY MONTH(event_date);

Résultat d’exemple :

7. Optimisation des performances

Recherche efficace à l’aide d’index

Créer un index sur une colonne de date peut améliorer considérablement les performances des requêtes.

Création d’un index

Le SQL suivant crée un index sur la colonne event_date.

CREATE INDEX idx_event_date ON events(event_date);

Vérification de l’effet d’un index

Vous pouvez utiliser EXPLAIN pour vérifier le plan d’exécution de la requête.

EXPLAIN SELECT * 
FROM events 
WHERE event_date = '2025-01-01';

Notes importantes lors de l’utilisation de fonctions

L’utilisation de fonctions dans la clause WHERE peut désactiver l’utilisation d’index.

Mauvais exemple

Dans la requête suivante, la fonction DATE() empêche l’utilisation de l’index.

SELECT * 
FROM events 
WHERE DATE(event_date) = '2025-01-01';

Exemple amélioré

Il est recommandé de comparer les valeurs directement sans utiliser de fonctions.

SELECT * 
FROM events 
WHERE event_date >= '2025-01-01' 
  AND event_date < '2025-01-02';

8. FAQ (Foire aux questions)

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

• R1 :
• DATE : Stocke uniquement la date ( YYYY-MM-DD ). Utilisez-le lorsque l’information d’heure n’est pas requise.
• DATETIME : Stocke à la fois la date et l’heure ( YYYY-MM-DD HH:MM:SS ). Utilisez-le lorsque le horodatage exact d’un événement est requis.

Exemple :

CREATE TABLE examples (
    example_date DATE,
    example_datetime DATETIME
);

Q2 : À quoi dois-je faire attention lors de la spécification d’une plage de dates avec BETWEEN ?

• R2 :
• Étant donné que BETWEEN inclut à la fois les dates de début et de fin, si la date de fin ne comprend pas de valeur d’heure, vous risquez de ne pas récupérer tous les enregistrements prévus.

Exemple :

-- This query may not retrieve records such as "2025-01-31 23:59:59"
SELECT * 
FROM events 
WHERE event_date BETWEEN '2025-01-01' AND '2025-01-31';

Amélioration :
Définissez explicitement l’heure de fin à 23:59:59 ou utilisez une comparaison qui ignore la partie heure.

SELECT * 
FROM events 
WHERE event_date >= '2025-01-01' AND event_date < '2025-02-01';

Q3 : Comment gérer les différences de fuseau horaire ?

• R3 : Dans MySQL, le type TIMESTAMP dépend du fuseau horaire. En revanche, le type DATETIME stocke une valeur fixe et n’est pas affecté par les fuseaux horaires.

Vérifier le réglage du fuseau horaire actuel :

SHOW VARIABLES LIKE 'time_zone';

Modifier le réglage du fuseau horaire :

SET time_zone = '+09:00'; -- Japan Standard Time (JST)

Q4 : Comment récupérer les données des 30 derniers jours ?

• R4 : Vous pouvez combiner CURDATE() ou NOW() avec INTERVAL pour récupérer les données des 30 derniers jours.

Exemple :

SELECT * 
FROM events 
WHERE event_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

Q5: Comment puis-je améliorer les performances des comparaisons de dates ?

• A5:
• Créer des index : Définir un index sur les colonnes de dates.
• Éviter d’utiliser des fonctions : L’utilisation de fonctions dans la clause WHERE peut désactiver les index, donc utiliser des comparaisons directes à la place.

9. Résumé

Points Clés de Cet Article

Cet article a fourni une explication complète des techniques de manipulation et de comparaison de dates dans MySQL. Les points clés sont les suivants :

1. Comprendre les caractéristiques et l’utilisation appropriée des types de données de date (DATE, DATETIME, TIMESTAMP).
2. Méthodes de comparaison de base ( = , > , < , BETWEEN , etc.).
3. Calculer les différences de dates ( DATEDIFF() , TIMESTAMPDIFF() ).
4. Améliorer les performances grâce à l’indexation et à une conception optimale des requêtes.

Nous espérons que ce guide vous aide à gérer efficacement les opérations sur les dates dans MySQL et à développer des requêtes pratiques et optimisées pour des applications du monde réel.