Comment travailler avec les dates et les heures en SQL

Introduction

Lorsque vous travaillez avec des bases de données relationnelles et le langage SQL (Structured Query Language), il peut arriver que vous ayez besoin de manipuler des valeurs représentant des dates ou des heures spécifiques. Par exemple, vous pourriez avoir besoin de calculer le total des heures passées sur une certaine activité, ou peut-être devez-vous manipuler des valeurs de date ou d’heure en utilisant des opérateurs mathématiques et des fonctions d’agrégation pour calculer leur somme ou leur moyenne.

Dans ce tutoriel, vous apprendrez comment utiliser les dates et les heures en SQL. Vous commencerez par effectuer des opérations arithmétiques et utiliser diverses fonctions avec des dates et des heures en utilisant uniquement l’instruction SELECT. Ensuite, vous pratiquerez en exécutant des requêtes sur des données d’exemple, et vous apprendrez à implémenter la fonction CAST pour rendre la sortie plus facile à lire.

Prérequis

Pour compléter ce tutoriel, vous aurez besoin de :

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
• MySQL installé et sécurisé sur le serveur. Suivez notre guide Comment installer MySQL sur Ubuntu 20.04 pour configurer cela. Ce guide suppose que vous avez également configuré un utilisateur MySQL non root, comme décrit dans Étape 3 de ce guide.

Pour pratiquer l’utilisation de la date et de l’heure dans ce tutoriel, vous aurez besoin d’une base de données et d’une table chargées avec des données d’exemple. Si vous n’en avez pas prêt à insérer, vous pouvez lire la section suivante Connexion à MySQL et configuration d’une base de données d’exemple pour apprendre comment créer une base de données et une table. Ce tutoriel fera référence à cette base de données et à cette table d’exemple tout au long.

Connexion à MySQL et configuration d’une base de données d’exemple

Si votre base de données SQL s’exécute sur un serveur distant, connectez-vous en SSH à partir de votre machine locale:

ssh sammy@your_server_ip

Ensuite, ouvrez l’invite MySQL, en remplaçant sammy par les informations de votre compte utilisateur MySQL:

mysql -u sammy -p

Créez une base de données nommée datetimeDB:

CREATE DATABASE datetimeDB;

Si la base de données a été créée avec succès, vous recevrez la sortie suivante:

Output
Query OK, 1 row affected (0.01 sec)

Pour sélectionner la base de données datetimeDB, exécutez la déclaration USE suivante:

USE datetimeDB;

Output
Database changed

Après avoir sélectionné la base de données, créez une table à l’intérieur. Pour l’exemple de ce tutoriel, nous allons créer une table qui contient les résultats de deux coureurs pour diverses courses qu’ils ont courues sur une année. Cette table aura les sept colonnes suivantes :

• race_id : affiche des valeurs du type de données int et sert de clé primaire de la table, ce qui signifie que chaque valeur dans cette colonne fonctionnera comme un identifiant unique pour sa ligne respective.
• runner_name : utilise le type de données varchar avec un maximum de 30 caractères pour les noms des deux coureurs, Bolt et Felix.
• race_name : contient les types de courses avec le type de données varchar avec un maximum de 20 caractères.
• start_day : utilise le type de données DATE pour suivre la date d’une course spécifique par année, mois et jour. Ce type de données respecte les paramètres suivants : quatre chiffres pour l’année et un maximum de deux chiffres pour le mois et le jour (AAAA-MM-JJ).
• start_time : représente l’heure de départ de la course avec le type de données TIME en heures, minutes et secondes (HH:MM:SS). Ce type de données suit un format d’horloge 24 heures, tel que 15:00 pour l’équivalent de 15h00.
• total_miles: montre le kilométrage total pour chaque course en utilisant le type de données decimal, car beaucoup des kilomètres totaux par course ne sont pas des nombres entiers. Dans ce cas, decimal spécifie une précision de trois avec une échelle de un, ce qui signifie que toutes les valeurs dans cette colonne peuvent avoir trois chiffres, avec l’un de ces chiffres étant à droite du point décimal.
• end_time: utilise le type de données TIMESTAMP pour suivre les temps des coureurs à la fin de la course. Ce type de données combine à la fois la date et l’heure dans une seule chaîne, et son format est une combinaison de ceux de DATE et TIME: (AAAA-MM-JJ HH:MM:SS).

Créez la table en exécutant la commande CREATE TABLE:

CREATE TABLE race_results (
race_id int, 
runner_name varchar(30),
race_name varchar(20), 
start_day DATE,
start_time TIME, 
total_miles decimal(3, 1),
end_time TIMESTAMP,
PRIMARY KEY (race_id)
); 

Ensuite, insérez quelques données d’exemple dans la table vide :

INSERT INTO race_results
(race_id, runner_name, race_name, start_day, start_time, total_miles, end_time)
VALUES
(1, 'bolt', '1600_meters', '2022-09-18', '7:00:00', 1.0, '2022-09-18 7:06:30'),
(2, 'bolt', '5K', '2022-10-19', '11:00:00', 3.1, '2022-10-19 11:22:31'),
(3, 'bolt', '10K', '2022-11-20', '10:00:00', 6.2, '2022-11-20 10:38:05'),
(4, 'bolt', 'half_marathon', '2022-12-21', '6:00:00', 13.1, '2022-12-21 07:39:04'),
(5, 'bolt', 'full_marathon', '2023-01-22', '8:00:00', 26.2, '2023-01-22 11:23:10'),
(6, 'felix', '1600_meters', '2022-09-18', '7:00:00', 1.0, '2022-09-18 7:07:15'),
(7, 'felix', '5K', '2022-10-19', '11:00:00', 3.1, '2022-10-19 11:30:50'),
(8, 'felix', '10K', '2022-11-20', '10:00:00', 6.2, '2022-11-20 11:10:17'),
(9, 'felix', 'half_marathon', '2022-12-21', '6:00:00', 13.1, '2022-12-21 08:11:57'),
(10, 'felix', 'full_marathon', '2023-01-22', '8:00:00', 26.2, '2023-01-22 12:02:10');

Output
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

Une fois que vous avez inséré les données, vous êtes prêt à commencer à pratiquer des opérations arithmétiques et des fonctions avec les dates et les heures en SQL.

Utilisation de l’arithmétique avec les dates et les heures

En SQL, vous pouvez manipuler les valeurs de date et d’heure en utilisant des expressions mathématiques. Tout ce qui est nécessaire est l’opérateur mathématique et les valeurs que vous souhaitez calculer.

À titre d’exemple, supposez que vous vouliez trouver une date qui est un certain nombre de jours après une autre. La requête suivante prend une valeur de date (2022-10-05) et y ajoute 17 pour retourner la valeur de la date dix-sept jours après celle spécifiée dans la requête. Notez que cet exemple spécifie 2022-10-05 comme une valeur de DATE pour s’assurer que le SGBD ne l’interprétera pas comme une chaîne de caractères ou un autre type de données :

SELECT DATE '2022-10-05' + 17 AS new_date;

Output
+----------+
| new_date |
+----------+
| 20221022 |
+----------+
1 row in set (0.01 sec)

Comme le montre cette sortie, 17 jours après le 2022-10-05 est le 2022-10-22, ou le 22 octobre 2022.

À titre d’autre exemple, supposons que vous vouliez calculer le nombre total d’heures entre deux moments différents. Vous pouvez le faire en soustrayant les deux heures l’une de l’autre. Pour la requête suivante, 11:00 est la première valeur de temps et 3:00 est la deuxième valeur de temps. Ici, vous devrez spécifier que les deux sont des valeurs de TIME afin de retourner la différence en heures :

SELECT TIME '11:00' - TIME '3:00' AS time_diff;

Output
+-----------+
| time_diff |
+-----------+
|     80000 |
+-----------+
1 row in set (0.00 sec)

Cette sortie vous indique que la différence entre 11:00 et 3:00 est de 80000, soit 8 heures.

Maintenant, pratiquez l’utilisation des opérations arithmétiques sur les informations de date et d’heure des données d’exemple. Pour la première requête, calculez le temps total mis par les coureurs pour terminer chaque course en soustrayant end_time de start_time :

SELECT runner_name, race_name, end_time - start_time 
AS total_time 
FROM race_results;

Output
+-------------+---------------+----------------+
| runner_name | race_name     | total_time     |
+-------------+---------------+----------------+
| bolt        | 1600_meters   | 20220918000630 |
| bolt        | 5K            | 20221019002231 |
| bolt        | 10K           | 20221120003805 |
| bolt        | half_marathon | 20221221013904 |
| bolt        | full_marathon | 20230122032310 |
| felix       | 1600_meters   | 20220918000715 |
| felix       | 5K            | 20221019003050 |
| felix       | 10K           | 20221120011017 |
| felix       | half_marathon | 20221221021157 |
| felix       | full_marathon | 20230122040210 |
+-------------+---------------+----------------+
10 rows in set (0.00 sec)

Vous remarquerez que cette sortie dans la colonne total_time est plutôt longue et difficile à lire. Plus tard, nous démontrerons comment utiliser la fonction CAST pour convertir ces valeurs de données afin qu’elles soient plus claires à lire.

Maintenant, si vous ne vous intéressez qu’aux performances de chaque coureur pour les courses plus longues, telles que les demi-marathons et les marathons complets, vous pouvez interroger vos données pour récupérer ces informations. Pour cette requête, soustrayez end_time de start_time, et affinez vos résultats en utilisant la clause WHERE pour récupérer les données où total_miles étaient supérieurs à 12 :

SELECT runner_name, race_name, end_time - start_time AS half_full_results
FROM race_results 
WHERE total_miles > 12;

Output
+-------------+---------------+-------------------+
| runner_name | race_name     | half_full_results |
+-------------+---------------+-------------------+
| bolt        | half_marathon |    20221221013904 |
| bolt        | full_marathon |    20230122032310 |
| felix       | half_marathon |    20221221021157 |
| felix       | full_marathon |    20230122040210 |
+-------------+---------------+-------------------+
4 rows in set (0.00 sec)

Dans cette section, vous avez effectué des calculs arithmétiques sur les dates et les heures avec l’instruction SELECT et à des fins pratiques sur les données d’exemple. Ensuite, vous pratiquerez les requêtes en utilisant diverses fonctions de date et d’heure.

Utilisation des fonctions de date et d’heure et des expressions d’intervalle

Il existe plusieurs fonctions qui peuvent être utilisées pour trouver et manipuler les valeurs de date et d’heure en SQL. Les fonctions SQL sont généralement utilisées pour traiter ou manipuler des données, et les fonctions disponibles dépendent de l’implémentation SQL. Cependant, la plupart des implémentations SQL vous permettent de trouver la date et l’heure actuelles en interrogeant les valeurs current_date et current_time.

Pour trouver la date d’aujourd’hui, par exemple, la syntaxe est courte et se compose uniquement de l’instruction SELECT et de la fonction current_date comme suit :

SELECT current_date;

Output
+--------------+
| current_date |
+--------------+
| 2022-02-15   |
+--------------+
1 row in set (0.00 sec)

En utilisant la même syntaxe, vous pouvez trouver l’heure actuelle avec la fonction current_time :

SELECT current_time;

Output
+--------------+
| current_time |
+--------------+
| 17:10:20     |
+--------------+
1 row in set (0.00 sec)

Si vous préférez interroger à la fois la date et l’heure dans la sortie, utilisez la fonction current_timestamp :

SELECT current_timestamp;

Output
+---------------------+
| current_timestamp   |
+---------------------+
| 2022-02-15 19:09:58 |
+---------------------+
1 row in set (0.00 sec)

Vous pouvez utiliser des fonctions de date et d’heure comme celles-ci dans des fonctions arithmétiques similaires à la section précédente. Par exemple, supposons que vous vouliez savoir quelle était la date il y a 11 jours à partir de la date d’aujourd’hui. Dans ce cas, vous pourriez utiliser la même structure syntaxique que celle utilisée précédemment pour interroger la fonction current_date puis soustraire 11 pour trouver la date d’il y a onze jours :

SELECT current_date - 11;

Output
+-------------------+
| current_date - 11 |
+-------------------+
|          20220206 |
+-------------------+
1 row in set (0.01 sec)

Comme le montre cette sortie, il y a 11 jours à partir de la current_date (au moment de la rédaction) était le 2022-02-06, ou le 6 février 2022. Essayez maintenant de lancer cette même opération, mais remplacez current_date par la fonction current_time :

SELECT current_time - 11;

Output
+-------------------+
| current_time - 11 |
+-------------------+
|        	233639 |
+-------------------+
1 row in set (0.00 sec)

Cette sortie montre qu’en soustrayant 11 de la valeur de current_time, il soustrait 11 secondes. L’opération que vous avez précédemment exécutée en utilisant la fonction current_date a interprété 11 comme des jours, et non comme des secondes. Cette incohérence dans la façon dont les nombres sont interprétés lors de la manipulation de fonctions de date et d’heure peut être déroutante. Au lieu de vous obliger à manipuler les valeurs de date et d’heure en utilisant l’arithmétique de cette manière, de nombreux systèmes de gestion de base de données vous permettent d’être plus explicite grâce à l’utilisation d’expressions INTERVAL.

Les expressions INTERVAL vous permettent de trouver quelle serait la date ou l’heure avant ou après un intervalle donné à partir d’une expression de date ou d’heure donnée. Elles doivent prendre la forme suivante :

INTERVAL value unit

Par exemple, pour trouver la date dans cinq jours à partir de maintenant, vous pourriez exécuter la requête suivante :

SELECT current_date + INTERVAL '5' DAY AS "5_days_from_today";

Cet exemple trouve la valeur current_date, puis ajoute l’expression d’intervalle INTERVAL '5' DAY à celle-ci. Cela renvoie la date dans 5 jours à partir de maintenant :

Output
+-------------------+
| 5_days_from_today |
+-------------------+
| 2022-03-06    	|
+-------------------+
1 row in set (0.00 sec)

Ceci est beaucoup moins ambigu que la requête suivante, qui produit une sortie similaire, bien que pas identique :

SELECT current_date + 5 AS "5_days_from_today";

Output
+-------------------+
| 5_days_from_today |
+-------------------+
|      	20220306 |
+-------------------+
1 row in set (0.00 sec)

Notez que vous pouvez également soustraire des intervalles de dates ou d’heures pour trouver des valeurs antérieures à la valeur de date spécifiée :

SELECT current_date - INTERVAL '7' MONTH AS "7_months_ago";

Output
+--------------+
| 7_months_ago |
+--------------+
| 2021-08-01   |
+--------------+
1 row in set (0.00 sec)

Les unités disponibles pour vous dans les expressions INTERVAL dépendent de votre choix de SGBD, bien que la plupart auront des options comme HOUR, MINUTE et SECOND :

SELECT current_time + INTERVAL '6' HOUR AS "6_hours_from_now",
current_time - INTERVAL '5' MINUTE AS "5_minutes_ago",
current_time + INTERVAL '20' SECOND AS "20_seconds_from_now";

Output
+------------------+---------------+---------------------+
| 6_hours_from_now | 5_minutes_ago | 20_seconds_from_now |
+------------------+---------------+---------------------+
| 07:51:43     	| 01:46:43  	| 01:52:03.000000 	|
+------------------+---------------+---------------------+
1 row in set (0.00 sec)

Maintenant que vous avez appris les expressions d’intervalle et quelques fonctions de date et d’heure, continuez à pratiquer en travaillant avec les données d’exemple que vous avez insérées à la première étape.

Utilisation de CAST et de fonctions d’agrégation avec date et heure

Rappelez-vous du troisième exemple dans la section Utilisation de l’arithmétique avec les dates et les heures, lorsque vous avez exécuté la requête suivante pour soustraire end_time de start_time afin de calculer les heures totales que chaque coureur a terminées par course. Cependant, la sortie a abouti à une colonne contenant une sortie très longue, qui suit le type de données TIMESTAMP qui a été configuré dans la table :

SELECT runner_name, race_name, end_time - start_time 
AS total_time 
FROM race_results;

Output
+-------------+---------------+----------------+
| runner_name | race_name     | total_time     |
+-------------+---------------+----------------+
| bolt        | 1600_meters   | 20220918000630 |
| bolt        | 5K            | 20221019002231 |
| bolt        | 10K           | 20221120003805 |
| bolt        | half_marathon | 20221221013904 |
| bolt        | full_marathon | 20230122032310 |
| felix       | 1600_meters   | 20220918000715 |
| felix       | 5K            | 20221019003050 |
| felix       | 10K           | 20221120011017 |
| felix       | half_marathon | 20221221021157 |
| felix       | full_marathon | 20230122040210 |
+-------------+---------------+----------------+
10 rows in set (0.00 sec)

Parce que vous effectuez une opération avec deux colonnes qui ont des types de données différents (end_time contenant des valeurs de type TIMESTAMP et start_time contenant des valeurs de type TIME), la base de données ne sait pas quel type de données utiliser lorsqu’elle imprime le résultat de l’opération. Au lieu de cela, elle convertit les deux valeurs en entiers afin de pouvoir effectuer l’opération, ce qui donne les longs nombres dans la colonne total_time.

Pour rendre ces données plus claires à lire et à interpréter, vous pouvez utiliser la fonction CAST pour convertir ces longues valeurs entières en type de données TIME. Pour ce faire, commencez par CAST puis suivez immédiatement une parenthèse ouvrante, les valeurs que vous voulez convertir, puis le mot-clé AS et le type de données auquel vous voulez le convertir.

La requête suivante est identique à l’exemple précédent, mais utilise une fonction CAST pour convertir la colonne total_time en type de données time:

SELECT runner_name, race_name, CAST(end_time - start_time AS time)
AS total_time 
FROM race_results;

Output
+-------------+---------------+------------+
| runner_name | race_name     | total_time |
+-------------+---------------+------------+
| bolt        | 1600_meters   | 00:06:30   |
| bolt        | 5K            | 00:22:31   |
| bolt        | 10K           | 00:38:05   |
| bolt        | half_marathon | 01:39:04   |
| bolt        | full_marathon | 03:23:10   |
| felix       | 1600_meters   | 00:07:15   |
| felix       | 5K            | 00:30:50   |
| felix       | 10K           | 01:10:17   |
| felix       | half_marathon | 02:11:57   |
| felix       | full_marathon | 04:02:10   |
+-------------+---------------+------------+
10 rows in set (0.00 sec)

CAST a converti les valeurs de données en TIME dans cette sortie, ce qui la rend beaucoup plus facile à lire et à comprendre.

Maintenant, utilisons quelques fonctions d’agrégation en combinaison avec la fonction CAST pour trouver le temps le plus court, le plus long et total de chaque coureur. Tout d’abord, interrogeons pour le minimum (ou le plus court) temps passé avec la fonction d’agrégation MIN. Encore une fois, vous voudrez utiliser CAST pour convertir les valeurs de données TIMESTAMP en valeurs de données TIME pour plus de clarté. Veuillez noter que lors de l’utilisation de deux fonctions comme dans cet exemple, deux paires de parenthèses sont nécessaires et le calcul des heures totales (end_time - start_time) doit être imbriqué dans l’une d’elles. Enfin, ajoutez une clause GROUP BY pour organiser ces valeurs en fonction de la colonne runner_name afin que la sortie présente les résultats de course des deux coureurs :

SELECT runner_name, MIN(CAST(end_time - start_time AS time)) AS min_time
FROM race_results GROUP BY runner_name;

Output
+-------------+----------+
| runner_name | min_time |
+-------------+----------+
| bolt        | 00:06:30 |
| felix       | 00:07:15 |
+-------------+----------+
2 rows in set (0.00 sec)

Cette sortie montre le temps de course le plus court de chaque coureur, dans ce cas un minimum de six minutes et 30 secondes pour Bolt, et sept minutes et 15 secondes pour Felix.

Ensuite, trouvez le temps de course le plus long de chaque coureur. Vous pouvez utiliser la même syntaxe que la requête précédente, mais cette fois remplacez MIN par MAX:

SELECT runner_name, MAX(CAST(end_time - start_time AS time)) AS max_time
FROM race_results GROUP BY runner_name;

Output
+-------------+----------+
| runner_name | max_time |
+-------------+----------+
| bolt        | 03:23:10 |
| felix       | 04:02:10 |
+-------------+----------+
2 rows in set (0.00 sec)

Cette sortie nous indique que le temps de course le plus long de Bolt était un total de trois heures, 23 minutes et 10 secondes ; et celui de Felix était un total de quatre heures, deux minutes et 10 secondes.

Maintenant, interrogeons quelques informations de haut niveau sur le total d’heures que chaque coureur a passées à courir. Pour cette requête, combinez la fonction d’agrégation SUM pour trouver la somme totale des heures basée sur end_time - start_time, et utilisez CAST pour convertir ces valeurs de données en TIME. N’oubliez pas d’inclure GROUP BY pour organiser les valeurs des résultats des deux coureurs:

SELECT runner_name, SUM(CAST(end_time - start_time AS time))
AS total_hours FROM race_results GROUP BY runner_name;

Output
+-------------+-------------+
| runner_name | total_hours |
+-------------+-------------+
| bolt        |       52880 |
| felix       |       76149 |
+-------------+-------------+
2 rows in set (0.00 sec)

Curieusement, cette sortie montre l’interprétation pour MySQL, qui calcule en fait le temps total sous forme d’entiers. Si nous lisons ces résultats comme du temps, le temps total de Bolt se décompose en cinq heures, 28 minutes et 80 secondes ; et le temps de Felix se décompose en sept heures, 61 minutes et 49 secondes. Comme vous pouvez le constater, cette décomposition du temps n’a pas de sens, ce qui indique qu’il est calculé comme un entier et non comme du temps. Si vous essayez cela dans un autre SGBD, comme PostgreSQL, par exemple, la même requête aurait un aspect légèrement différent:

SELECT runner_name, SUM(CAST(end_time - start_time AS time))
AS total_hours FROM race_results GROUP BY runner_name;

Output
 runner_name | total_hours
-------------+-------------
 felix       | 10:01:44
 bolt        | 06:09:20
(2 rows)

Dans ce cas, la requête dans PostgreSQL interprète les valeurs comme du temps et les calcule en conséquence, de sorte que les résultats de Felix se décomposent en un total de 10 heures, une minute et 44 secondes ; et ceux de Bolt en six heures, neuf minutes et 20 secondes. C’est un exemple de la manière dont différentes implémentations de SGBD peuvent interpréter différemment les valeurs de données même si elles utilisent la même requête et le même jeu de données.

Conclusion

Comprendre comment utiliser la date et l’heure en SQL est utile lors de l’interrogation pour des résultats spécifiques tels que les minutes, les secondes, les heures, les jours, les mois, les années ; ou une combinaison de tous ceux-ci. De plus, il existe de nombreuses fonctions disponibles pour les dates et les heures qui facilitent la recherche de certaines valeurs, comme la date ou l’heure actuelle. Bien que ce tutoriel utilise uniquement l’arithmétique d’addition et de soustraction sur les dates et les heures en SQL, vous pouvez utiliser des valeurs de date et d’heure avec n’importe quelle expression mathématique. Apprenez-en davantage dans notre guide sur les expressions mathématiques et les fonctions d’agrégation et essayez-les avec vos requêtes de date et d’heure.