Comment travailler efficacement avec les dates et les heures dans MySQL

11 février 2021 Hibou Gourou

Les dates et les heures sont importantes, elles aident à garder les choses organisées et font partie intégrante de toute opération logicielle.

Travailler efficacement avec eux dans la base de données peut parfois sembler déroutant, qu'il s'agisse de travailler sur les différents fuseaux horaires, d'ajouter / soustraire des dates et d'autres opérations.

Découvrez les différentes fonctions MySQL disponibles pour gérer et gérer facilement les dates / heures dans votre base de données.

Travailler avec des fuseaux horaires

Pour aider à garder les choses standardisées, vous ne devriez jamais travailler avec des dates / heures dans le fuseau horaire UTC. Chaque fois que vous établissez une connexion à la base de données MySQL, vous devez basculer le fuseau horaire sur UTC, ce qui peut être fait avec l'instruction SQL suivante:

 SET TIME_ZONE = '+0:00'

Étant donné que toutes les dates seront désormais enregistrées au format UTC, vous savez toujours avec quoi vous travaillez, ce qui rend les choses plus simples et directes.

Si nécessaire, vous pouvez facilement convertir le fuseau horaire de n'importe quelle valeur de date / heure / horodatage avec la fonction MySQL pratique CONVERT_TZ () . Vous devez d'abord connaître le décalage, par exemple, PST sur la côte ouest de l'Amérique du Nord est UTC -08: 00, vous pouvez donc utiliser:

 SELECT CONVERT_TZ('2021-02-04 21:47:23', '+0:00', '-8:00');

Cela se traduit par 2021-02-04 13:47:23, ce qui est exactement correct. Les trois arguments passés à CONVERT_TZ () sont d'abord le datetime / timestamp avec lequel vous commencez (utilisez now () pour l'heure actuelle), le second sera toujours '+0: 00' puisque toutes les dates sont forcées à UTC dans la base de données , et le dernier est le décalage dans lequel nous souhaitons convertir la date.

Ajouter / soustraire des dates

Plusieurs fois, vous devez ajouter ou soustraire des dates, par exemple si vous devez récupérer des enregistrements d'il y a une semaine ou planifier quelque chose dans un mois.

Heureusement, MySQL a les excellentes fonctions DATE_ADD () et DATE_SUB () , ce qui rend cette tâche extrêmement facile. Par exemple, vous pouvez soustraire deux semaines de la date actuelle avec l'instruction SQL:

 SELECT DATE_SUB(now(), interval 2 week);

Si à la place vous vouliez ajouter trois jours à un horodatage existant, vous utiliseriez:

 SELECT DATE_ADD('2021-02-07 11:52:06', interval 3 day);

Les deux fonctions fonctionnent de la même manière, le premier argument est l'horodatage avec lequel vous commencez et le deuxième argument est l'intervalle à ajouter ou à soustraire. Le deuxième argument est toujours formaté de la même manière en commençant par l' intervalle de mots suivi d'une valeur numérique et de l'intervalle lui-même, qui peut être l'un des suivants: seconde, minute, heure, jour, semaine, mois, trimestre, année.

Pour un autre exemple, si vous souhaitez récupérer toutes les connexions qui se sont produites au cours des 34 dernières minutes, vous pouvez utiliser une instruction SQL telle que:

 SELECT * FROM logins WHERE login_date >= DATE_SUB(now(), interval 45 minute);

Comme vous pouvez le voir, cela récupérerait tous les enregistrements de la table des connexions avec une date de connexion supérieure à l'heure actuelle moins 45 minutes, ou en d'autres termes, les 45 dernières minutes.

Obtenir la différence entre les dates

Parfois, vous devez savoir combien de temps s'est écoulé entre deux dates. Vous pouvez facilement obtenir le nombre de jours entre deux dates différentes avec la fonction DATEDIFF , telle que l'instruction SQL ci-dessous:

 SELECT DATEDIFF(now(), '2020-12-15');

La fonction DATEDIFF prend deux arguments, qui sont tous deux des horodatages et donne le nombre de jours entre eux. L'exemple ci-dessus montrera le nombre de jours écoulés du 15 décembre 2020 à aujourd'hui.

Pour obtenir le nombre de secondes entre deux dates, la fonction TO_SECONDS () peut être utile, par exemple:

 SELECT TO_SECONDS(now()) - TO_SECONDS('2021-02-05 11:56:41');

Cela entraînera le nombre de secondes entre les deux dates fournies.

Extraire des segments à partir de dates

Il existe diverses fonctions MySQL qui vous permettent d'extraire facilement des segments spécifiques de dates, par exemple si vous ne vouliez que le mois, le jour de l'année ou l'heure. Voici quelques exemples de telles fonctions:

 SELECT MONTH('2021-02-11 15:27:52');

 SELECT HOUR(now());

 SELECT DAYOFYEAR('2021-07-15 12:00:00');

Les instructions SQL ci-dessus donneraient 02 , l'heure actuelle, et 196 car le 15 septembre est le 196e jour de l'année. Voici une liste de toutes les fonctions d'extraction de date disponibles, chacune ne prenant qu'un seul argument, la date étant extraite de:

 - SECOND()
 - MINUTE()
 - HOUR()
 - DAY()
 - WEEK() - Number 0 - 52 defining the week within the year.
 - MONTH()
 - QUARTER() - Number 1 - 4 defining the quarter of the year.
 - YEAR()
 - DAYOFYEAR() - The day of the year (eg. Sept 15th = 196).
 - LAST_DAY() - The last day in the given month.
 - DATE() - The date in YYYY-MM-DD format without the time.
 - TIME() The time in HH:II:SS format without the date.
 - TO_DAYS() - The number of days since AD 0.
 - TO_SECONDS() - The number of seconds since AD 0.
 - UNIX_TIMESTAMP() - The number of seconds since the epoch (Jan 1st, 1970)

Par exemple, si vous souhaitez uniquement récupérer le mois et l'année de création de tous les utilisateurs, vous pouvez utiliser une instruction SQL telle que:

 SELECT id, MONTH(created_at), YEAR(created_at) FROM users;

Cela récupérerait tous les enregistrements de la table des utilisateurs et afficherait le numéro d'identification, le mois et l'année dans lesquels chaque utilisateur a été créé.

Regroupement des enregistrements par période de date

Une excellente utilisation des fonctions de date est la possibilité de regrouper les enregistrements par période de date en utilisant GROUP BY dans vos instructions SQL. Par exemple, vous souhaitez peut-être extraire le montant total de toutes les commandes en 2020 regroupées par mois. Vous pouvez utiliser une instruction SQL telle que:

 SELECT MONTH(created_at), SUM(amount) FROM orders WHERE created_at BETWEEN '2020-01-01 00:00:00' AND '2020-12-31 23:59:59' GROUP BY MONTH(created_at);

Cela permettrait de récupérer toutes les commandes passées en 2020, de les regrouper par mois de création et de renvoyer 12 enregistrements indiquant le montant total commandé chaque mois de l'année.

Veuillez noter que pour de meilleures performances d'index, il est toujours préférable d'éviter d'utiliser des fonctions de date telles que YEAR () dans la clause WHERE des instructions SQL, et d'utiliser à la place l'opérateur BETWEEN comme indiqué dans l'exemple ci-dessus.

Ne soyez plus jamais confondu avec les dates

En utilisant les connaissances ci-dessus, vous pouvez désormais travailler efficacement avec, traduire et effectuer des opérations dans des dates et des heures dans un large éventail de cas d'utilisation.

N'oubliez pas de toujours utiliser UTC lorsque vous travaillez avec des dates pour plus de simplicité, et utilisez les conseils ci-dessus pour gérer efficacement les dates dans votre logiciel, que ce soit pour effectuer des calculs simples ou pour extraire facilement des rapports regroupés par périodes de date.

Si vous êtes un peu nouveau dans SQL, assurez-vous de consulter ces commandes SQL essentielles pour vous aider à améliorer votre utilisation de SQL.