Fonctions de date et d'heure en SQL

Leçon 3.4 · Temps de lecture : ~8 min · Exercices pratiques ↓

Les fonctions de date et d'heure SQL renvoient la date actuelle, extraient des parties d'une date, ajoutent et soustraient des intervalles, calculent l'écart entre deux dates et formatent les dates pour l'affichage. Dans cette leçon, vous apprendrez les fonctions les plus utilisées sur les données de location de la base Sakila, et vous verrez comment leurs noms diffèrent d'un SGBD à l'autre.

Presque toutes les tables métier contiennent des dates : quand une commande a été passée, quand un film a été loué, quand un paiement a été effectué. Les questions comme « combien de temps a duré la location ? », « quand faut-il rendre le film ? » ou « combien de locations en juillet ? » se résolvent avec des fonctions de date.

Les exemples utilisent la syntaxe MySQL et MariaDB. Les fonctions de date varient davantage entre SGBD que tout autre groupe de fonctions ; un tableau comparatif figure donc à la fin de la leçon.

La date et l'heure actuelles

SELECT CURRENT_DATE AS today,
       CURRENT_TIME AS now_time,
       NOW() AS now_datetime;

Résultat : une ligne avec la date, l'heure, et la date avec l'heure du serveur de base de données.

CURRENT_DATE, CURRENT_TIME et CURRENT_TIMESTAMP s'écrivent sans parenthèses : ce sont des expressions de la norme SQL. La valeur est figée au début de l'instruction, donc toutes les lignes d'une même requête reçoivent la même heure.

Extraire des parties d'une date

DATE() et TIME() séparent une valeur date-heure en date et en heure ; YEAR(), MONTH() et DAY() renvoient des nombres :

SELECT rental_date,
       DATE(rental_date) AS rental_day,
       TIME(rental_date) AS rental_time,
       YEAR(rental_date) AS rental_year,
       MONTH(rental_date) AS rental_month,
       DAY(rental_date) AS day_of_month
FROM rental
LIMIT 3;

Résultat : pour chacune des trois premières locations, la date-heure complète décomposée en date, heure, année, mois et jour.

La fonction standard EXTRACT() fait la même chose et fonctionne dans la plupart des SGBD :

SELECT rental_date,
       EXTRACT(YEAR FROM rental_date) AS rental_year,
       EXTRACT(MONTH FROM rental_date) AS rental_month
FROM rental
LIMIT 3;

Les parties extraites sont utiles dans les filtres :

SELECT rental_id, rental_date
FROM rental
WHERE YEAR(rental_date) = 2005
  AND MONTH(rental_date) = 7
LIMIT 5;

Résultat : cinq locations effectuées en juillet 2005.

Une fonction appliquée à une colonne empêche la base de données d'utiliser un index sur cette colonne. Sur de grandes tables, un intervalle de dates est plus rapide : WHERE rental_date >= '2005-07-01' AND rental_date < '2005-08-01'. Vous verrez pourquoi dans les leçons sur les index.

Ajouter et soustraire des intervalles

DATE_ADD() et DATE_SUB() décalent une date d'un intervalle. L'unité peut être SECOND, MINUTE, HOUR, DAY, WEEK, MONTH ou YEAR.

SELECT rental_id,
       rental_date,
       DATE_ADD(rental_date, INTERVAL 7 DAY) AS due_date,
       DATE_SUB(rental_date, INTERVAL 1 MONTH) AS month_before
FROM rental
LIMIT 3;

Résultat : la date de chaque location, la date sept jours plus tard et la date un mois plus tôt.

MySQL et MariaDB acceptent aussi la forme courte rental_date + INTERVAL 7 DAY.

L'écart entre deux dates

DATEDIFF(end, start) renvoie le nombre de jours entre deux dates et ignore l'heure :

SELECT rental_id,
       rental_date,
       return_date,
       DATEDIFF(return_date, rental_date) AS days_rented
FROM rental
WHERE return_date IS NOT NULL
LIMIT 5;

Résultat : combien de jours calendaires a duré chacune de cinq locations rendues.

TIMESTAMPDIFF(unit, start, end) compte des unités complètes de n'importe quelle taille ; notez que la date de début vient en premier :

SELECT rental_id,
       TIMESTAMPDIFF(HOUR, rental_date, return_date) AS hours_rented,
       TIMESTAMPDIFF(DAY, rental_date, return_date) AS full_days_rented
FROM rental
WHERE return_date IS NOT NULL
LIMIT 5;

Résultat : la durée de location en heures et en journées complètes de 24 heures. full_days_rented peut être inférieur à DATEDIFF, qui compte les jours calendaires.

Formater les dates

DATE_FORMAT(date, format) transforme une date en texte selon le format donné :

SELECT rental_date,
       DATE_FORMAT(rental_date, '%d.%m.%Y') AS european_date,
       DATE_FORMAT(rental_date, '%M %e, %Y %H:%i') AS long_date
FROM rental
LIMIT 3;

Résultat : la date sous la forme 24.05.2005 et May 24, 2005 22:53.

Le résultat de DATE_FORMAT() est du texte, pas une date. Formatez les dates uniquement pour l'affichage ; comparez et triez les valeurs de date d'origine.

Les fonctions de date dans les autres SGBD

EXTRACT(part FROM date) et CURRENT_DATE / CURRENT_TIMESTAMP sont standard et fonctionnent presque partout.

Points clés :

• CURRENT_DATE, CURRENT_TIMESTAMP et NOW() renvoient la date et l'heure actuelles.
• DATE(), YEAR(), MONTH(), DAY() et EXTRACT() extraient des parties d'une date.
• DATE_ADD() et DATE_SUB() décalent une date d'un intervalle.
• DATEDIFF() compte les jours calendaires ; TIMESTAMPDIFF() compte des unités complètes de toute taille.
• DATE_FORMAT() renvoie du texte : utilisez-le pour l'affichage, pas pour les comparaisons.
• Les noms des fonctions de date varient beaucoup selon les SGBD ; consultez le tableau ci-dessus quand vous changez de base.

Questions fréquentes

Comment obtenir la date du jour en SQL ?

Utilisez CURRENT_DATE pour la date seule, et CURRENT_TIMESTAMP ou NOW() pour la date avec l'heure. Dans SQL Server, utilisez GETDATE() ou CAST(GETDATE() AS DATE).

Comment calculer le nombre de jours entre deux dates en SQL ?

Dans MySQL et MariaDB, utilisez DATEDIFF(end_date, start_date). Dans SQL Server, DATEDIFF(day, start_date, end_date). Dans PostgreSQL, soustrayez une date de l'autre : end_date::date - start_date::date.

Comment ajouter des jours à une date en SQL ?

Dans MySQL et MariaDB, utilisez DATE_ADD(date, INTERVAL 7 DAY) ou date + INTERVAL 7 DAY. Dans PostgreSQL, date + INTERVAL '7 days' ; dans SQL Server, DATEADD(day, 7, date).