Comment travailler efficacement avec les dates et les heures dans MySQL

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 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 au sein de votre base de données.





Utilisation 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 changer le fuseau horaire en 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 plus simples.



applications de messagerie texte gratuites pour tablettes Android

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 maintenant () 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

Plusieurs fois, vous devez ajouter ou soustraire des dates, par exemple si vous devez récupérer des enregistrements d'une semaine auparavant 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 à 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 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 du 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.

Obtenez 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 le DATE IFF fonction, telle que l'instruction SQL ci-dessous :

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

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

Pour obtenir le nombre de secondes entre deux dates, le TO_SECONDS() La 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 :

SELECT MONTH('2021-02-11 15:27:52'); SELECT HOUR(now()); SELECT DAYOFYEAR('2021-07-15 12:00:00');

Les instructions SQL ci-dessus entraîneraient 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 A.D. 0.
- TO_SECONDS() - The number of seconds since A.D. 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 permettrait de récupérer tous les enregistrements dans le utilisateurs table et affiche 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 PAR GROUPE 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 au cours de l'année 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 ANNÉE() dans la clause WHERE des instructions SQL, et utilisez à la place le ENTRE 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, traduire et effectuer des opérations dans les dates et les 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 de date.

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

Partager Partager Tweeter E-mail Comment créer des rapports et des documents professionnels dans Microsoft Word

Ce guide examine les éléments d'un rapport professionnel et passe en revue la structuration, le style et la finalisation de votre document dans Microsoft Word.

Lire la suite Rubriques connexes A propos de l'auteur Matt Dizak(18 articles publiés) Plus de Matt Dizak

Abonnez-vous à notre newsletter

Rejoignez notre newsletter pour des conseils techniques, des critiques, des ebooks gratuits et des offres exclusives !

Cliquez ici pour vous abonner