Guide MySQL sur les dates et heures : types de données, fonctions, formatage et requêtes de plage

1. Introduction

MySQL est un système de gestion de bases de données largement utilisé pour créer des applications web et des bases de données, et la gestion correcte des données de type date est particulièrement importante. Par exemple, les données liées aux dates apparaissent dans de nombreux scénarios : gestion des articles de blog, suivi de l’historique des ventes de produits et stockage de l’historique de connexion des utilisateurs. En gérant correctement les données de date, vous pouvez traiter les informations efficacement et fournir des renseignements précis aux utilisateurs.

Dans ce guide, nous expliquerons de manière exhaustive tout, des types de données date/heure de base et de l’utilisation des fonctions de date dans MySQL aux calculs basés sur les dates et aux requêtes de plage. Ce contenu est conçu pour les débutants à intermédiaires, et nous inclurons des exemples de requêtes pratiques basés sur des cas d’utilisation réels — le rendant utile pour le travail quotidien.

En lisant cet article, vous serez capable de :

• Comprendre les caractéristiques des types de données date/heure de MySQL et choisir le type approprié pour vos données.
• Utiliser les fonctions de date pour récupérer et manipuler facilement la date actuelle, les dates passées et les dates futures.
• Extraire des données pour des périodes spécifiques en effectuant des recherches et des comparaisons sur des plages de dates.

Passons maintenant aux fondamentaux des dates MySQL à partir de la section suivante.

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

Lors de la gestion des dates dans MySQL, il est important de choisir le bon type de données date/heure en fonction des données et du cas d’utilisation. MySQL propose plusieurs types de données pour manipuler les dates et les heures, chacun avec des caractéristiques et des usages différents. Dans cette section, nous expliquerons en détail les principaux types date/heure et leurs cas d’utilisation typiques.

DATE

Le type DATE ne stocke que la date (année, mois, jour) et n’inclut pas de composante horaire. L’intervalle supporté va de « 1000‑01‑01 » à « 9999‑12‑31 », ce qui permet de gérer les dates de l’an 1000 à l’an 9999. Il convient aux informations où seule la date est pertinente, comme les anniversaires ou les dates de naissance.

CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    birth_date DATE
);

TIME

Le type TIME stocke l’heure (heures, minutes, secondes). L’intervalle supporté va de « ‑838:59:59 » à « 838:59:59 ». Parce qu’il peut représenter des temps négatifs, il peut également être utilisé pour exprimer des différences de temps. Il est utile pour enregistrer les heures de travail ou la durée des tâches.

CREATE TABLE work_log (
    id INT,
    task_name VARCHAR(50),
    duration TIME
);

DATETIME

Le type DATETIME stocke à la fois la date et l’heure. L’intervalle supporté va de « 1000‑01‑01 00:00:00 » à « 9999‑12‑31 23:59:59 ». Il ne dépend pas des fuseaux horaires, et vous pouvez récupérer la valeur stockée exactement telle qu’elle a été enregistrée. Il convient pour consigner les horodatages d’événements passés ou de programmations futures.

CREATE TABLE appointments (
    id INT,
    description VARCHAR(50),
    appointment_datetime DATETIME
);

TIMESTAMP

Le type TIMESTAMP stocke la date et l’heure et convertit automatiquement les valeurs en fonction du fuseau horaire du serveur. L’intervalle supporté va de « 1970‑01‑01 00:00:01 UTC » à « 2038‑01‑19 03:14:07 UTC ». Il est compatible avec le temps Unix epoch et convient pour enregistrer les horodatages et l’historique des modifications. De plus, comme TIMESTAMP peut définir l’heure actuelle par défaut, il est pratique pour enregistrer automatiquement les moments de création et de mise à jour.

CREATE TABLE posts (
    id INT,
    title VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

YEAR

Le type YEAR ne stocke que l’année. L’intervalle supporté va de « 1901 » à « 2155 ». Il est utile lorsqu’on veut représenter une année précise, comme l’année de fabrication ou l’année de création d’une entreprise.

CREATE TABLE products (
    id INT,
    name VARCHAR(50),
    manufactured_year YEAR
);

Comparaison et cas d’utilisation de chaque type date/heure

Résumé

Les types de données date/heure de MySQL doivent être choisis en fonction des caractéristiques de vos données et de la manière dont vous prévoyez de les utiliser. Dans la section suivante, nous examinerons de plus près les différences entre DATETIME et TIMESTAMP, qui sont particulièrement faciles à confondre.

3. Différences entre DATETIME et TIMESTAMP

Lorsqu’on travaille avec des dates et des heures dans MySQL, DATETIME et TIMESTAMP sont couramment utilisés, mais il existe d’importantes différences entre eux. Les deux stockent l’année, le mois, le jour, l’heure, la minute et la seconde, mais ils diffèrent dans la façon dont ils gèrent les fuseaux horaires et la plage de temps qu’ils peuvent enregistrer. Vous devez choisir en fonction de votre cas d’utilisation. Dans cette section, nous expliquerons en détail les différences et les caractéristiques de DATETIME et TIMESTAMP.

Caractéristiques de DATETIME

• Pas dépendant des fuseaux horaires : Le type DATETIME n’est pas affecté par le réglage du fuseau horaire du serveur. Vous pouvez récupérer la valeur stockée exactement telle qu’elle a été enregistrée.
• Plage : Il prend en charge du 01‑01‑1000 00:00:00 au 31‑12‑9999 23:59:59.
• Cas d’utilisation : Convient aux données que vous souhaitez stocker sans dépendre d’un fuseau horaire particulier, comme les événements historiques ou les plannings futurs.

Exemple : Stockage d’une date/heure d’événement

Par exemple, si vous souhaitez conserver une valeur de date/heure « universelle » telle quelle, DATETIME est un bon choix. L’exemple suivant enregistre un événement prévu à une date et une heure précises.

CREATE TABLE events (
    id INT,
    event_name VARCHAR(50),
    event_datetime DATETIME
);

Dans cette table, la date/heure stockée dans event_datetime n’est pas affectée par les fuseaux horaires, et la valeur est récupérée exactement telle qu’elle a été enregistrée.

Caractéristiques de TIMESTAMP

• Dépendant des fuseaux horaires : Le type TIMESTAMP est stocké et récupéré en fonction du fuseau horaire du serveur. Lors du transfert de données entre serveurs situés dans des fuseaux différents, une conversion s’effectuera en conséquence.
• Plage : Il prend en charge du 01‑01‑1970 00:00:01 UTC au 19‑01‑2038 03:14:07 UTC (basé sur la plage de temps Unix).
• Prise en charge de la mise à jour automatique : En utilisant DEFAULT CURRENT_TIMESTAMP et ON UPDATE CURRENT_TIMESTAMP, MySQL peut enregistrer automatiquement le timestamp actuel lors de l’insertion ou de la mise à jour des données.
• Cas d’utilisation : Idéal pour suivre automatiquement la date de création ou de mise à jour, par exemple lorsque vous souhaitez qu’un timestamp change à chaque mise à jour d’un enregistrement.

Exemple : Stockage des heures de création et de mise à jour des articles

Cet exemple utilise la fonctionnalité de mise à jour automatique de TIMESTAMP pour enregistrer les heures de création et de mise à jour des articles de blog.

CREATE TABLE blog_posts (
    id INT,
    title VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Avec ce réglage, created_at est enregistré lors de la création initiale de l’article, et updated_at est automatiquement mis à jour à chaque fois que l’article est modifié.

Tableau de comparaison : DATETIME vs TIMESTAMP

Comment choisir

• Choisissez DATETIME si vous ne voulez pas d’effets de fuseau horaire : Par exemple, si vous souhaitez stocker l’heure d’un événement comme une heure locale fixe pour un pays/région spécifique, DATETIME peut être approprié.
• Choisissez TIMESTAMP si vous avez besoin de mise à jour automatique ou de gestion des fuseaux horaires : Par exemple, si vous voulez enregistrer automatiquement quand les lignes de la base de données sont mises à jour, TIMESTAMP est pratique.

Résumé

DATETIME et TIMESTAMP ont des caractéristiques différentes. En choisissant celui qui convient à votre besoin, vous pouvez gérer les données date/heure plus efficacement. Dans la section suivante, nous examinerons les fonctions de date essentielles dans MySQL.

4. Notions de base sur les fonctions date/heure de MySQL

MySQL propose de nombreuses fonctions pour travailler avec les dates et les heures — de la récupération de la date/heure actuelle à l’ajout ou la soustraction d’intervalles. Ces fonctions sont utiles pour l’administration et l’analyse de bases de données. Dans cette section, nous expliquerons les bases des fonctions date/heure de MySQL et leurs cas d’utilisation typiques.

Fonctions pour obtenir la date/heure actuelle

Trois fonctions courantes pour récupérer la date et l’heure actuelles dans MySQL sont NOW(), CURDATE() et CURTIME().

NOW()

La fonction NOW() renvoie la date et l’heure actuelles au format YYYY-MM-DD HH:MM:SS. Elle est utile pour la journalisation et l’horodatage des enregistrements.

SELECT NOW(); -- Get the current date and time

CURDATE()

La fonction CURDATE() renvoie la date actuelle au format YYYY-MM-DD. Elle n’inclut pas l’heure, ce qui la rend adaptée lorsque vous avez seulement besoin de la date.

SELECT CURDATE(); -- Get the current date

CURTIME()

La fonction CURTIME() renvoie l’heure actuelle au format HH:MM:SS. Utilisez‑la lorsque vous avez besoin uniquement de l’heure sans la date.

SELECT CURTIME(); -- Get the current time

Functions to Add/Subtract Dates

Pour ajouter ou soustraire des intervalles à une date, utilisez DATE_ADD() et DATE_SUB(). Ces fonctions facilitent le calcul de dates futures ou passées.

DATE_ADD()

La fonction DATE_ADD() ajoute un intervalle spécifié à une date. Par exemple, elle est utile pour obtenir la date 7 jours plus tard ou 1 mois plus tard.

SELECT DATE_ADD('2023-01-01', INTERVAL 7 DAY); -- Returns 2023-01-08

DATE_SUB()

La fonction DATE_SUB() soustrait un intervalle spécifié d’une date. Utilisez‑la pour calculer des dates passées.

SELECT DATE_SUB('2023-01-01', INTERVAL 1 MONTH); -- Returns 2022-12-01

Function to Calculate Date Differences

Pour calculer la différence entre deux dates, DATEDIFF() est pratique. Par exemple, vous pouvez calculer le nombre de jours écoulés depuis une date précise, ou le nombre de jours entre deux dates.

DATEDIFF()

La fonction DATEDIFF() renvoie la différence entre deux dates en jours. Elle est utile lorsque vous souhaitez confirmer le nombre de jours entre une date de début et une date de fin.

SELECT DATEDIFF('2023-01-10', '2023-01-01'); -- Returns 9

Other Useful Date Functions

MySQL fournit d’autres fonctions de date utiles.

EXTRACT()

La fonction EXTRACT() extrait une partie spécifique (année, mois, jour, etc.) d’une date. Elle est utile lorsque vous avez besoin uniquement d’une portion d’une date.

SELECT EXTRACT(YEAR FROM '2023-01-01'); -- Extract year (2023)
SELECT EXTRACT(MONTH FROM '2023-01-01'); -- Extract month (1)
SELECT EXTRACT(DAY FROM '2023-01-01'); -- Extract day (1)

DATE_FORMAT()

La fonction DATE_FORMAT() affiche une date dans un format spécifié. Elle est utile lorsque vous voulez afficher les dates dans un format particulier (par exemple, le format à la japonaise).

SELECT DATE_FORMAT('2023-01-01', '%Y-%m-%d'); -- Returns 2023-01-01

5. Date Formatting and Conversion Methods

Dans MySQL, vous pouvez modifier la façon dont les dates sont affichées pour les rendre plus lisibles, ou convertir des chaînes de caractères en types date. Cela vous permet de contrôler flexiblement les formats d’affichage et de gérer les données de manière cohérente même lorsque les entrées proviennent de formats différents. Dans cette section, nous expliquerons les principales fonctions utilisées pour le formatage et la conversion des dates.

Date Formatting with the DATE_FORMAT() Function

En utilisant DATE_FORMAT(), vous pouvez afficher les valeurs de date dans un format spécifié. C’est particulièrement utile lorsque vous avez besoin d’un format d’affichage différent du format standard, comme le format japonais « YYYY年MM月DD日 ».

Format Options

Avec DATE_FORMAT(), vous pouvez utiliser les options de format suivantes :

• %Y : année sur 4 chiffres
• %y : année sur 2 chiffres
• %m : mois sur 2 chiffres (01–12)
• %d : jour sur 2 chiffres (01–31)
• %H : heure sur 2 chiffres (00–23)
• %i : minute sur 2 chiffres (00–59)
• %s : seconde sur 2 chiffres (00–59)

Example Usage

Par exemple, pour afficher la date 2023-01-01 sous la forme « 2023年01月01日 », vous pouvez écrire :

SELECT DATE_FORMAT('2023-01-01', '%Y年%m月%d日'); -- Returns 2023年01月01日

Vous pouvez également afficher une date et une heure avec des barres obliques, comme « 2023/01/01 12:30:45 ».

SELECT DATE_FORMAT('2023-01-01 12:30:45', '%Y/%m/%d %H:%i:%s'); -- Returns 2023/01/01 12:30:45

Conversion de chaînes en dates avec STR_TO_DATE()

La fonction STR_TO_DATE() convertit une chaîne de date en un type date MySQL. Par exemple, elle est utile lorsque vous souhaitez traiter une chaîne comme « 2023年01月01日 » comme une valeur DATE.

Exemple d’utilisation

Pour convertir la chaîne « 2023-01-01 » en une valeur DATE, écrivez ce qui suit :

SELECT STR_TO_DATE('2023-01-01', '%Y-%m-%d'); -- Returns 2023-01-01 as a DATE

Pour convertir une chaîne de style japonais comme « 2023年01月01日 » en une date, spécifiez le format correspondant :

SELECT STR_TO_DATE('2023年01月01日', '%Y年%m月%d日'); -- Returns 2023-01-01 as a DATE

Exemples de conversion de dates à partir de différents formats

Dans une utilisation réelle, les formats d’entrée de dates peuvent varier, il peut donc être nécessaire de convertir à partir de différents formats. Voici quelques exemples.

1. Convertir « YYYY/MM/DD » en une valeur DATE

   SELECT STR_TO_DATE('2023/01/01', '%Y/%m/%d'); -- Returns 2023-01-01 as a DATE
   

2. Convertir « MM-DD-YYYY » en une valeur DATE

   SELECT STR_TO_DATE('01-01-2023', '%m-%d-%Y'); -- Returns 2023-01-01 as a DATE
   

Ainsi, même si les données sont saisies dans différents formats, vous pouvez les stocker et les gérer de manière cohérente dans un format de date unifié.

Différences entre DATE_FORMAT et STR_TO_DATE, et quand les utiliser

• DATE_FORMAT() : Utilisé pour modifier le format d’affichage des valeurs de date existantes. Comme le changement n’affecte que l’affichage, les données stockées elles‑elles ne sont pas modifiées.
• STR_TO_DATE() : Utilisé pour convertir une chaîne de date en une valeur MySQL DATE ou DATETIME. Cela modifie le type de données stockées et facilite le traitement des dates avec d’autres fonctions et requêtes MySQL.

Résumé

En utilisant DATE_FORMAT() et STR_TO_DATE(), vous pouvez gérer de manière flexible l’affichage et le stockage des données de date. Cela permet de traiter les entrées utilisateur ou système de façon plus souple tout en maintenant une gestion cohérente des dates. Dans la section suivante, nous expliquerons les calculs et comparaisons de dates, et examinerons de plus près comment calculer et comparer des périodes à l’aide des données de date.

6. Calculs et comparaisons de dates

MySQL propose plusieurs fonctions pratiques pour les calculs et comparaisons de dates. Elles permettent d’extraire des données pour des périodes spécifiques ou de calculer des différences de dates à des fins d’analyse. Dans cette section, nous présenterons les principales fonctions utilisées pour les calculs et comparaisons de dates, ainsi que leur utilisation.

Fonction de calcul des différences de dates : DATEDIFF()

La fonction DATEDIFF() renvoie la différence entre deux dates en « jours ». Elle est utile lorsque vous souhaitez vérifier le nombre de jours écoulés entre des dates ou déterminer combien de jours se sont écoulés depuis un événement passé jusqu’à aujourd’hui.

Exemple d’utilisation

Par exemple, pour calculer le nombre de jours du 1 janvier 2023 au 10 janvier 2023, écrivez ce qui suit :

SELECT DATEDIFF('2023-01-10', '2023-01-01'); -- Returns 9

Dans cet exemple, la différence entre la date de début et la date de fin est de 9 jours, donc le résultat est 9.

Calcul des différences par unité avec TIMESTAMPDIFF()

La fonction TIMESTAMPDIFF() calcule la différence entre deux dates ou datetime dans une unité spécifiée (année, mois, jour, heure, minute, seconde). Par exemple, vous pouvez calculer les différences en « mois » ou en « heures » selon les besoins.

Exemple d’utilisation

1. Calculer une différence en mois

   SELECT TIMESTAMPDIFF(MONTH, '2022-01-01', '2023-01-01'); -- Returns 12
   

2. Calculer une différence en heures

   SELECT TIMESTAMPDIFF(HOUR, '2023-01-01 00:00:00', '2023-01-02 12:00:00'); -- Returns 36
   

Comparaison de dates

Les comparaisons de dates sont effectuées à l’aide des opérateurs de comparaison standard de MySQL (<, >, <=, >=, =). Cela vous permet d’extraire des données sur une période spécifique ou d’inclure des dates passées/futures comme conditions.

Exemple d’utilisation

Par exemple, pour extraire les données « à partir du 1 janvier 2023 », écrivez ce qui suit :

SELECT * FROM events WHERE event_date >= '2023-01-01';

Spécification d’une plage avec BETWEEN

L’opérateur BETWEEN vous permet de spécifier une plage de dates pour récupérer des données. Il est utile lorsque vous souhaitez extraire des enregistrements qui se situent dans une période spécifique.

Example Usage

Par exemple, pour récupérer des données du 1 janvier 2023 au 31 janvier 2023, écrivez ce qui suit :

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

Calculs de dates avec ADDDATE() et SUBDATE()

Vous pouvez utiliser ADDDATE() et SUBDATE() pour ajouter ou soustraire un nombre de jours à une date. Elles sont pratiques pour calculer des dates futures ou passées.

Example Usage

1. Ajouter 10 jours à une date

   SELECT ADDDATE('2023-01-01', 10); -- Returns 2023-01-11
   

2. Soustraire 10 jours d’une date

   SELECT SUBDATE('2023-01-01', 10); -- Returns 2022-12-22
   

Résumé

En utilisant les fonctions de calcul et de comparaison de dates de MySQL, vous pouvez extraire efficacement des données pour des périodes spécifiques et analyser les données par intervalles de temps. Dans la section suivante, nous examinerons de plus près le sujet plus avancé des « recherches par plage de dates ».