Comment travailler efficacement avec les dates et les heures dans MySQL

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 manipuler et gérer facilement les dates/heures au sein de votre base de données.

Travailler avec les fuseaux horaires

Pour aider à garder les choses standardisées, vous ne devriez jamais travailler qu'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 en 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 datetime / timestamp avec le pratique CONVERT_TZ() Fonction MySQL. 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 tout à fait correct. Les trois arguments passés à CONVERT_TZ() sont d'abord la date/heure avec laquelle vous commencez (utilisez now() pour l'heure actuelle), la seconde sera toujours '+0:00' puisque toutes les dates sont forcées à UTC dans la base de données, et la dernière est le décalage vers lequel nous souhaitons convertir la date.

Ajouter / Soustraire des dates

Souvent, 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 l'excellent DATE_ADD() et DATE_SUB() fonctions rendant cette tâche extrêmement facile. Par exemple, vous pouvez soustraire deux semaines de la date actuelle avec l'instruction SQL :

SELECT DATE_SUB(maintenant(), intervalle 2 semaines); 

Si vous vouliez plutôt ajouter trois jours à un horodatage existant, vous utiliseriez :

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

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 le mot intervalle 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 des connexions table 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 avez besoin de savoir combien de temps s'est écoulé entre deux dates. Vous pouvez facilement obtenir le nombre de jours entre deux dates différentes avec le DATEDIFF fonction, telle que l'instruction SQL ci-dessous :

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

Le DATEDIFF La fonction 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 depuis le 15 décembre 2020 jusqu'à aujourd'hui.

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

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

Cela se traduira par le nombre de secondes entre les deux dates fournies.

Extraire les segments des dates

Il existe diverses fonctions MySQL qui vous permettent d'extraire facilement des segments spécifiques à partir 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 :

SÉLECTIONNER LE MOIS('2021-02-11 15:27:52'); 

SELECT HEURE(maintenant()); 

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 :

- SECONDE() 
 - MINUTES() 
 - HEURE() 
 - JOURNÉE() 
 - WEEK() - Nombre 0 - 52 définissant la semaine dans l'année. 
 - MOIS() 
 - QUARTER() - Nombre 1 - 4 définissant le trimestre de l'année. 
 - AN() 
 - DAYOFYEAR() - Le jour de l'année (ex. 15 septembre =196). 
 - LAST_DAY() - Le dernier jour du mois donné. 
 - DATE() - La date au format AAAA-MM-JJ sans l'heure. 
 - TIME() L'heure au format HH:II:SS sans la date. 
 - TO_DAYS() - Le nombre de jours depuis A.D. 0. 
 - TO_SECONDS() - Le nombre de secondes depuis A.D. 0. 
 - UNIX_TIMESTAMP() - Le nombre de secondes depuis l'époque (1er janvier 1970) 

Par exemple, si vous souhaitez peut-être 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 utilisateurs ; 

Cela récupérerait tous les enregistrements dans les utilisateurs table et affiche le numéro d'identification, le mois et l'année de création de chaque utilisateur.

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 à l'aide de 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 commandes WHERE created_at BETWEEN '2020-01-01 00:00:00' AND '2020-12-31 23:59 :59' GROUPER PAR MOIS(created_at); 

Cela récupèrerait toutes les commandes passées en 2020, les regrouperait par mois de création et renverrait 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 utilisez plutôt BETWEEN opérateur 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, traduire et effectuer efficacement des opérations de dates et d'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 extraire facilement des rapports regroupés par périodes.

Si vous débutez avec SQL, assurez-vous de consulter ces commandes SQL essentielles pour vous aider à améliorer votre utilisation de SQL.