Améliorez vos compétences en codage avec des valeurs temporelles dans MySQL

Les utilisateurs débutants et expérimentés du populaire système de base de données MySQL peuvent souvent être confus quant à la manière dont les valeurs temporelles sont gérées par la base de données. Parfois, les utilisateurs ne prennent pas la peine d’apprendre beaucoup sur les types de données de valeur temporelle. C’est peut-être parce qu’ils pensent qu’il n’y a pas grand-chose à savoir sur eux. Un rendez-vous est un rendez-vous, non ? Eh bien, pas toujours. Prendre quelques minutes pour apprendre comment MySQL stocke et affiche les dates et les heures est bénéfique. Apprendre à tirer le meilleur parti des valeurs temporelles dans vos tables de base de données peut vous aider à devenir un meilleur codeur.

Types de données temporelles MySQL

Lorsque vous construisez vos tables dans MySQL, vous choisissez le type de données approprié qui contient le plus efficacement les données que vous avez l’intention d’insérer dans la table (INT, FLOAT, CHAR,et ainsi de suite). MySQL vous propose cinq types de données pour les valeurs temporelles. Ils sont: DATE, TIME, DATETIME, TIMESTAMPet YEAR.

MySQL utilise le ISO 8601 format pour stocker les valeurs dans les formats suivants :

• DATE AAAA-MM-JJ
• TEMPS HH:MM:SS
• HORODATAGE AAAA-MM-JJ HH:MM:SS
• ANNÉE AAAA

Datetime par rapport à l’horodatage

Vous avez peut-être remarqué que le DATETIME et TIMESTAMP les types de données contiennent les mêmes données. Vous pourriez vous demander s’il y a des différences entre les deux. Il y a des différences.

Premièrement, la plage de dates pouvant être utilisée diffère. DATETIME peut contenir des dates entre 1000-01-01 00:00:00 et 9999-12-31 23:59:59, alors que TIMESTAMP a une plage beaucoup plus limitée de 1970-01-01 00:00:01 à 2038-01-19 03:14:07 UTC.

Deuxièmement, bien que les deux types de données vous permettent de auto_initialize ou auto_update leurs valeurs respectives (avec DEFAULT CURRENT_TIMESTAMP et ON UPDATE CURRENT_TIMESTAMP respectivement), cela n’était pas disponible pour DATETIME valeurs jusqu’à la version 5.6.5. Vous pouvez utiliser l’un des synonymes MySQL pour CURRENT_TIMESTAMP si vous choisissez, comme NOW() ou LOCALTIME().

[ Download now: MariaDB and MySQL cheat sheet ]

Si tu utilises ON UPDATE CURENT_TIMESTAMP (ou l’un de ses synonymes) pour un DATETIME valeur, mais n’utilisez pas la DEFAULT CURRENT_TIMESTAMP clause, alors la colonne sera par défaut NULL. Cela se produit sauf si vous incluez NOT NULL dans la définition de table, auquel cas sa valeur par défaut est zéro.

Une autre chose importante à garder à l’esprit est que bien que normalement ni un DATETIME ni un TIMESTAMP colonne ont une valeur par défaut sauf si vous en déclarez une, il y a une exception à cette règle. La première TIMESTAMP colonne dans votre table est implicitement créée avec les deux DEFAULT CURRENT_TIMESTAMP et ON UPDATE CURRENT_TIMESTAMP clauses si aucune n’est spécifiée et si la variable explicit_defaults_for_timestamp est désactivé.

Pour vérifier l’état de cette variable, exécutez :

mysql> show variables like 'explicit_default%';

Si vous voulez l’activer ou le désactiver, exécutez ce code, en utilisant 0 pour désactiver et 1 pour activer :

mysql> set explicit_defaults_for_timestamp = 0;

Temps

MySQL TIME Le type de données peut sembler assez simple, mais il y a quelques points qu’un bon programmeur doit garder à l’esprit.

Tout d’abord, sachez que bien que le temps soit souvent considéré comme l’heure de la journée, il s’agit en fait du temps écoulé. En d’autres termes, il peut s’agir d’une valeur négative ou supérieure à 23:59:59. UN TIME La valeur dans MySQL peut être comprise entre -838:59:59 et 838:59:59.

De plus, si vous abrégez une valeur de temps, MySQL l’interprète différemment selon que vous utilisez ou non un deux-points. Par exemple, la valeur 10:34 est vue par MySQL comme 10:34:00. C’est-à-dire dix heures trente-quatre minutes. Mais si vous omettez les deux-points, 1034′, MySQL voit cela comme 00:10:34. Soit dix minutes et 34 secondes.

Enfin, il faut savoir que TIME valeurs (ainsi que la partie temporelle de DATETIME et TIMESTAMP colonnes) peut, depuis la version 5.6.4, prendre une unité fractionnaire. Pour l’utiliser, ajoutez un entier (valeur maximale six) entre parenthèses à la fin de la définition du type de données.

time_column TIME(2)

Fuseaux horaires

Les changements de fuseau horaire causent non seulement de la confusion et de la fatigue dans le monde réel, mais sont également connus pour causer des problèmes dans les systèmes de base de données. La terre est divisée en 24 fuseaux horaires distincts qui changent généralement tous les 15 degrés de longitude. Je dis généralement parce que certaines nations choisissent de faire les choses différemment. La Chine, par exemple, opère sous un seul fuseau horaire au lieu des cinq qui seraient attendus.

La question est de savoir comment gérer les utilisateurs d’un système de base de données qui se trouvent dans des fuseaux horaires différents. Heureusement, MySQL ne rend pas cela trop difficile.

Pour vérifier le fuseau horaire de votre session, exécutez :

mysql> select @@session.time_zone;

S’il dit Systemcela signifie qu’il utilise le fuseau horaire défini dans votre my.cnf fichier de configuration. Si vous exécutez votre serveur MsSQL sur votre ordinateur local, c’est probablement ce que vous obtiendrez, et vous n’avez pas besoin d’apporter de modifications.

Si vous souhaitez modifier le fuseau horaire de votre session, exécutez une commande telle que :

mysql> set time_zone="-05:00";

Cela définit votre fuseau horaire à cinq heures de retard sur UTC. (États-Unis/Est).

Obtenir le jour de la semaine

Pour suivre le code dans le reste de ce didacticiel, vous devez créer une table avec des valeurs de date sur votre système. Par exemple:

mysql> create table test
( row_id smallint not null auto_increment primary key,
the_date date not null);

Insérez ensuite des dates aléatoires dans le tableau en utilisant le format ISO 8601, telles que :

mysql> insert into test (the_date) VALUES ('2022-01-05');

J’ai mis quatre lignes de valeurs de date dans ma table de test, mais en mettre autant ou autant que vous le souhaitez.

Parfois, vous souhaiterez peut-être savoir à quel jour de la semaine correspond un jour particulier. MySQL vous offre quelques options.

La première, et peut-être la plus évidente, consiste à utiliser le DAYNAME() fonction. En utilisant le tableau d’exemple, DAYNAME() vous indique le jour de la semaine pour chacune des dates :

mysql> SELECT the_date, DAYNAME(the_date) FROM test ;
+------------+-------------------------------+
| the_date   | DAYNAME(the_date)             |
+------------+-------------------------------+
| 2021-11-02 | Tuesday                       |
| 2022-01-05 | Wednesday                     |
| 2022-05-03 | Tuesday                       |
| 2023-01-13 | Friday                        |
+------------+-------------------------------+
4 rows in set (0.00 sec)

Les deux autres méthodes pour obtenir le jour de la semaine renvoient des valeurs entières au lieu du nom du jour. Ils sont WEEKDAY() et DAYOFWEEK(). Ils renvoient tous les deux des nombres, mais ils ne renvoient pas le même nombre. Le WEEKDAY() La fonction renvoie un nombre compris entre 0 et 6, 0 étant lundi et 6 étant dimanche. D’autre part, DAYOFWEEK() renvoie un nombre compris entre 1 et 7, 1 correspondant à dimanche et 7 à samedi.

mysql> SELECT the_date, DAYNAME(the_date),
WEEKDAY(the_date), DAYOFWEEK(the_date) FROM test;
+------------+------------------+------------------+--------------------+
| the_date   | DAYNAME(the_date)| WEEKDAY(the_date)| DAYOFWEEK(the_date)|
| 2021-11-02 | Tuesday          | 1                | 3                  |
| 2022-01-05 | Wednesday        | 2                | 4                  |
| 2022-05-03 | Tuesday          | 1                | 3                  |
| 2023-01-13 | Friday           | 4                | 6                  |
+------------+------------------+------------------+--------------------+
4 rows in set (0.00 sec)

Quand vous ne voulez qu’une partie de la date

Parfois, vous pouvez avoir une date stockée dans votre table MySQL, mais vous ne souhaitez accéder qu’à une partie de la date. Ce n’est pas un problème.

Il existe plusieurs fonctions bien nommées dans MySQL qui permettent un accès facile à une partie particulière d’un objet date. Pour montrer juste quelques exemples :

mysql> SELECT the_date, YEAR(the_date), MONTHNAME(the_date), 
DAYOFMONTH(the_date) FROM test ;
+-----------+---------------+-------------------+---------------------+
| the_date  | YEAR(the_date)|MONTHNAME(the_date)| DAYOFMONTH(the_date)|
+-----------+---------------+-------------------+---------------------+
| 2021-11-02| 2021          | November          | 2                   |
| 2022-01-05| 2022          | January           | 5                   |
| 2022-05-03| 2022          | May               | 3                   |
| 2023-01-13| 2023          | January           | 13                  |
+-----------+---------------+-------------------+---------------------+
4 rows in set (0.00 sec)

MySQL vous permet également d’utiliser le EXTRACT() function pour accéder à une partie d’une date. Les arguments que vous fournissez à la fonction sont un spécificateur d’unité (assurez-vous qu’il est singulier), FROM, et le nom de la colonne. Ainsi, pour obtenir uniquement l’année de notre table de test, vous pouvez écrire :

mysql> SELECT EXTRACT(YEAR FROM the_date) FROM test;
+----------------------------------------------+
| EXTRACT(YEAR FROM the_date)                  |
+----------------------------------------------+
| 2021                                         |
| 2022                                         |
| 2022                                         |
| 2023                                         |
+----------------------------------------------+
4 rows in set (0.01 sec)

Insertion et lecture de dates avec différents formats

Comme mentionné précédemment, MySQL stocke les valeurs de date et d’heure en utilisant le ISO 8601 format. Mais que se passe-t-il si vous souhaitez stocker les valeurs de date et d’heure d’une autre manière, comme MM-DD-YYYY pour les dates ? Eh bien, tout d’abord, n’essayez pas. MySQL stocke les dates et les heures dans le 8601 format et c’est ainsi. N’essayez pas de changer cela. Cependant, cela ne signifie pas que vous devez convertir vos données dans ce format particulier avant de les entrer dans votre base de données, ou que vous ne pouvez pas afficher les données dans le format que vous souhaitez.

Si vous souhaitez entrer une date dans votre tableau qui est formatée d’une manière non ISO, vous pouvez utiliser STR_TO_DATE(). Le premier argument est la valeur de chaîne de la date que vous souhaitez stocker dans votre base de données. Le deuxième argument est la chaîne de formatage qui permet à MySQL de savoir comment la date est organisée. Regardons un exemple rapide, puis j’approfondirai un peu plus en quoi consiste cette chaîne de formatage étrange.

mysql> insert into test (the_date) values (str_to_date('January 13, 2023','%M %d, %Y'));

Query OK, 1 row affected (0.00 sec)

Vous placez la chaîne de formatage entre guillemets et faites précéder chacun des caractères spéciaux d’un signe de pourcentage. La séquence de format dans le code ci-dessus indique à MySQL que ma date consiste en un nom de mois complet (%M)suivi d’un jour à deux chiffres (%d)puis une virgule et enfin une année à quatre chiffres (%Y). Notez que la capitalisation est importante.

Certains des autres caractères de chaîne de formatage couramment utilisés sont :

• %b nom abrégé du mois (exemple : Jan)
• %c mois numérique (exemple : 1)
• %W nom du jour (exemple : samedi)
• %a nom abrégé du jour (exemple : sam)
• %T Heure de 24 heures (exemple : 22:01:22)
• %r Heure 12 heures avec AM/PM (exemple : 22:01:22 PM)
• %y Année à 2 chiffres (exemple : 23)

Notez que pour l’année à 2 chiffres (%y) la plage d’années va de 1970 à 2069. Ainsi, les nombres de 70 à 99 sont supposés être du 20e siècle, tandis que les nombres de 00 à 69 sont supposés être du 21e siècle.

Si vous avez une date stockée dans votre base de données et que vous souhaitez l’afficher dans un format différent, vous pouvez utiliser le DATE_FORMAT() fonction:

mysql> SELECT DATE_FORMAT(the_date, '%W, %b. %d, %y') FROM test;
+-----------------------------------------+
| DATE_FORMAT(the_date, '%W, %b. %d, %y') |
+-----------------------------------------+
| Tuesday, Nov. 02, 21                    |
| Wednesday, Jan. 05, 22                  |
| Tuesday, May. 03, 22                    |
| Friday, Jan. 13, 23                     |
+-----------------------------------------+
4 rows in set (0.00 sec)

Conclusion

Ce tutoriel devrait vous donner un aperçu utile des valeurs de date et d’heure dans MySQL. J’espère que cet article vous a appris quelque chose de nouveau qui vous permet à la fois d’avoir un meilleur contrôle et une meilleure compréhension de la façon dont votre base de données MySQL gère les valeurs temporelles.