Une fois que je me suis surpris à penser que lorsque je travaillais avec le temps dans des bases de données, j'utilisais presque toujours du temps précis à la seconde simplement parce que j'étais habitué et que c'était l'option décrite dans la documentation et un grand nombre d'exemples. Cependant, cette précision est désormais loin d'être suffisante pour toutes les tâches. Les systèmes modernes sont complexes - ils peuvent être constitués de nombreuses parties, avoir des millions d'utilisateurs interagissant avec eux - et dans de nombreux cas, il est plus pratique d'utiliser une plus grande précision, dont le support existe depuis longtemps.
Dans cet article, je parlerai des moyens d'utiliser le temps avec des parties fractionnaires d'une seconde dans MySQL et PHP. Il a été conçu comme un tutoriel, donc le matériel est conçu pour un large éventail de lecteurs et, à certains endroits, répète la documentation. La valeur principale devrait être que j'ai rassemblé dans un seul texte tout ce que vous devez savoir pour travailler avec ce temps dans MySQL, PHP et le framework Yii, et j'ai également ajouté des descriptions des problèmes non évidents que vous pourriez rencontrer.
J'utiliserai le terme «temps de haute précision». Dans la documentation MySQL, vous verrez le terme «secondes fractionnelles», mais sa traduction littérale semble étrange, mais je n'ai pas trouvé une autre traduction établie.
Quand utiliser le temps de haute précision?
Pour commencer, je vais montrer une capture d'écran de la boîte de réception de ma boîte de réception, qui illustre bien l'idée:
Les lettres sont la réaction d'une même personne à un événement. Un homme a accidentellement appuyé sur le mauvais bouton, s'en est vite rendu compte et s'est corrigé. En conséquence, nous avons reçu deux lettres envoyées à peu près en même temps, qui sont importantes pour trier correctement. Si l'heure d'envoi est la même, il est possible que les lettres soient affichées dans le mauvais ordre et que le destinataire soit gêné, car alors il recevra le mauvais résultat pour lequel il comptera.
Je suis tombé sur les situations suivantes dans lesquelles un temps de grande précision serait pertinent:
- Vous souhaitez mesurer le temps entre certaines opérations. Ici, tout est très simple: plus la précision des horodatages aux limites de l'intervalle est élevée, plus la précision du résultat est élevée. Si vous utilisez des secondes entières, vous pouvez faire une erreur pendant 1 seconde (si vous tombez sur les frontières des secondes). Si vous utilisez six décimales, l'erreur sera inférieure de six ordres de grandeur.
- Vous avez une collection où il est probable qu'il y ait plusieurs objets avec le même temps de création. Un exemple est un chat familier à tout le monde, où la liste des contacts est triée par heure du dernier message. S'il apparaît une navigation page par page, il existe même un risque de perte de contacts aux bordures de page. Ce problème peut être résolu sans temps de haute précision grâce au tri et à la pagination par une paire de champs (temps + identifiant unique d'un objet), mais cette solution a ses inconvénients (au moins la complication des requêtes SQL, mais pas seulement). Augmenter la précision du temps contribuera à réduire la probabilité de problèmes et à éviter de compliquer le système.
- Vous devez conserver un historique des modifications de certains objets. Ceci est particulièrement important dans le monde des services, où les modifications peuvent se produire en parallèle et à des endroits complètement différents. À titre d'exemple, je peux travailler avec des photos de nos utilisateurs, où de nombreuses opérations différentes peuvent être effectuées en parallèle (l'utilisateur peut rendre la photo privée ou la supprimer, elle peut être modérée dans l'un des nombreux systèmes, recadrée pour être utilisée comme photo dans le chat, etc.). )
Il faut garder à l'esprit que l'on ne peut pas croire les valeurs obtenues à 100% et la précision réelle des valeurs obtenues peut être inférieure à six décimales. Cela est dû au fait que nous pouvons obtenir une valeur d'heure inexacte (en particulier lorsque vous travaillez dans un système distribué composé de nombreux serveurs), l'heure peut changer de manière inattendue (par exemple, lors de la synchronisation via NTP ou lors du changement d'horloge), etc. Je ne m'attarderai pas sur tous ces problèmes, mais je donnerai quelques articles où vous pourrez en savoir plus à leur sujet:
Travailler avec un temps de haute précision dans MySQL
MySQL prend en charge trois types de colonnes dans lesquelles l'heure peut être stockée: TIME , DATETIME et TIMESTAMP . Initialement, ils ne pouvaient stocker que des valeurs qui étaient des multiples d'une seconde (par exemple, 2019-08-14 19:20:21). Dans la version 5.6.4, sortie en décembre 2011, il est devenu possible de travailler avec la fraction de seconde. Pour ce faire, lors de la création de la colonne, vous devez spécifier le nombre de décimales, qui doivent être stockées dans la partie fractionnaire de l'horodatage. Le nombre maximal de caractères pris en charge est de six, ce qui vous permet de stocker un temps précis à la microseconde près. Si vous essayez d'utiliser plus de caractères, vous obtenez une erreur.
Un exemple:
Test> CREATE TABLE `ChatContactsList` ( `chat_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, `title` varchar(255) NOT NULL, `last_message_send_time` timestamp(2) NULL DEFAULT NULL ) ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) Test> ALTER TABLE `ChatContactsList` MODIFY last_message_send_time TIMESTAMP(9) NOT NULL; ERROR 1426 (42000): Too-big precision 9 specified for 'last_message_send_time'. Maximum is 6. Test> ALTER TABLE `ChatContactsList` MODIFY last_message_send_time TIMESTAMP(3) NOT NULL; Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 Test> INSERT INTO ChatContactsList (title, last_message_send_time) VALUES ('Chat #1', NOW()); Query OK, 1 row affected (0.03 sec) Test> SELECT * FROM ChatContactsList; +
Dans cet exemple, l'horodatage de l'enregistrement inséré a une fraction nulle. Cela s'est produit car la valeur d'entrée a été indiquée à la seconde près. Pour résoudre le problème, la précision de la valeur d'entrée doit être identique à celle de la base de données. Le conseil semble évident, mais il est pertinent, car un problème similaire peut apparaître dans les applications réelles: nous étions confrontés à une situation où la valeur d'entrée avait trois décimales et six étaient stockées dans la base de données.
Le moyen le plus simple d'éviter ce problème est d'utiliser les valeurs d'entrée avec une précision maximale (jusqu'à quelques microsecondes). Dans ce cas, lors de l'écriture des données dans la table, l'heure sera arrondie à la précision requise. Il s'agit d'une situation tout à fait normale qui ne provoquera aucun avertissement:
Test> UPDATE ChatContactsList SET last_message_send_time="2019-09-22 22:23:15.2345" WHERE chat_id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 Test> SELECT * FROM ChatContactsList; +
Lorsque vous utilisez l'initialisation automatique et la mise à jour automatique des colonnes TIMESTAMP à l'aide d'une structure de la forme DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP il est important que les valeurs aient la même précision que la colonne elle-même:
Test> ALTER TABLE ChatContactsList ADD COLUMN updated TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; ERROR 1067 (42000): Invalid default value for 'updated' Test> ALTER TABLE ChatContactsList ADD COLUMN updated TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6); ERROR 1067 (42000): Invalid default value for 'updated' Test> ALTER TABLE ChatContactsList ADD COLUMN updated TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3); Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 Test> UPDATE ChatContactsList SET last_message_send_time='2019-09-22 22:22:22' WHERE chat_id=1; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 Test> SELECT * FROM ChatContactsList; +
Les fonctions MySQL pour travailler dans le temps permettent de travailler avec la partie fractionnaire des unités de mesure. Je ne les énumérerai pas tous (je suggère de regarder dans la documentation), mais je donnerai quelques exemples:
Test> SELECT NOW(2), NOW(4), NOW(4) + INTERVAL 7.5 SECOND; +
Le principal problème associé à l'utilisation de la partie fractionnaire des secondes dans les requêtes SQL est l'incohérence de la précision dans les comparaisons ( > , < , BETWEEN ). Vous pouvez le rencontrer si les données de la base de données ont une précision et dans les requêtes - une autre. Voici un petit exemple illustrant ce problème:
Dans cet exemple, la précision des valeurs de la requête est supérieure à la précision des valeurs de la base de données et le problème se produit «à la frontière par le haut». Dans la situation opposée (si la valeur d'entrée a une précision inférieure à la valeur dans la base de données), il n'y aura pas de problème - MySQL apportera la valeur à la précision souhaitée dans INSERT et SELECT:
Test> INSERT INTO ChatContactsList (title, last_message_send_time) VALUES ('Chat #3', '2019-09-03 21:20:19.1'); Query OK, 1 row affected (0.00 sec) Test> SELECT title, last_message_send_time FROM ChatContactsList WHERE last_message_send_time <= '2019-09-03 21:20:19.1'; +
La cohérence de l'exactitude des valeurs doit toujours être gardée à l'esprit lorsque vous travaillez avec un temps de haute précision. Si ces problèmes de limites sont critiques pour vous, vous devez vous assurer que le code et la base de données fonctionnent avec le même nombre de décimales.
Réflexions sur le choix de la précision dans les colonnes avec des parties fractionnaires de secondesLa quantité d'espace occupée par la partie fractionnaire d'une unité de temps dépend du nombre de caractères dans la colonne. Il semble naturel de choisir des significations familières: trois ou six décimales. Mais dans le cas de trois personnages, ce n'est pas si simple. En fait, MySQL utilise un octet pour stocker deux décimales:
Exigences de stockage de type de date et d'heure
Il s'avère que si vous sélectionnez trois décimales, vous n'utilisez pas pleinement l'espace occupé et pour la même surcharge, vous pouvez prendre quatre caractères. En général, je vous recommande de toujours utiliser un nombre pair de caractères et, si nécessaire, de «rogner» ceux qui ne sont pas nécessaires lors de la sortie. L'option idéale est de ne pas être gourmand et de prendre six décimales. Dans le pire des cas (avec le type DATETIME), cette colonne occupera 8 octets, c'est-à-dire la même chose que l'entier dans la colonne BIGINT.
Voir aussi:
Travailler avec un temps de haute précision en PHP
Il ne suffit pas d'avoir du temps de haute précision dans la base de données - vous devez pouvoir travailler avec lui dans le code de vos programmes. Dans cette section, je parlerai de trois points principaux:
- Réception et formatage de l'heure: je vais expliquer comment obtenir l'horodatage avant de le mettre dans la base de données, de le récupérer à partir de là et de faire une sorte de manipulation.
- Travailler avec le temps dans PDO: je vais vous montrer un exemple de la façon dont PHP prend en charge le formatage du temps dans une bibliothèque de base de données.
- Travailler avec le temps dans les frameworks: je vais parler de l'utilisation du temps dans les migrations pour changer la structure de la base de données.
Lorsque vous travaillez avec le temps, vous devez pouvoir effectuer plusieurs opérations de base:
- obtenir l'heure actuelle;
- obtenir un instant dans le temps d'une chaîne formatée;
- ajouter une période à un point dans le temps (ou soustraire une période);
- obtenir une chaîne formatée pour un point dans le temps.
Dans cette partie, je vais vous dire quelles sont les possibilités pour effectuer ces opérations en PHP.
La première consiste à travailler avec un horodatage sous forme de nombre . Dans ce cas, dans le code PHP, nous travaillons avec des variables numériques, que nous opérons à travers des fonctions telles que l' time , la date , strtotime . Cette méthode ne peut pas être utilisée pour travailler avec un temps de haute précision, car dans toutes ces fonctions, les horodatages sont un entier (ce qui signifie que la partie fractionnelle en eux sera perdue).
Voici les signatures des principales fonctions de la documentation officielle:
time ( void ) : int
https://www.php.net/manual/ru/function.time.php
strtotime ( string $time [, int $now = time() ] ) : int
http://php.net/manual/ru/function.strtotime.php
date ( string $format [, int $timestamp = time() ] ) : string
https://php.net/manual/ru/function.date.php
strftime ( string $format [, int $timestamp = time() ] ) : string
https://www.php.net/manual/ru/function.strftime.php
Un point intéressant sur la fonction dateBien qu'il soit impossible de passer la fraction de seconde à l'entrée de ces fonctions, dans la ligne du modèle de formatage passé à l'entrée de la fonction date , vous pouvez définir des caractères pour afficher les millisecondes et microsecondes. Lors du formatage, les zéros seront toujours renvoyés à leur place.
Un exemple:
$now = time(); print date('Ymd H:i:s.u', $now);
Cette méthode inclut microtime hrtime microtime et hrtime , qui vous permettent d'obtenir un hrtime avec une partie fractionnaire pour l'instant en cours. Le problème est qu'il n'existe aucun moyen prêt à l'emploi pour formater une telle étiquette et l'obtenir à partir d'une chaîne d'un format spécifique. Cela peut être résolu en implémentant indépendamment ces fonctions, mais je n'envisagerai pas une telle option.
Si vous devez travailler uniquement avec des minuteries, la bibliothèque HRTime est une bonne option, que je ne considérerai pas plus en détail en raison des limites de son utilisation. Je peux seulement dire qu'il vous permet de travailler avec du temps à la nanoseconde et garantit la monotonie des minuteries, ce qui élimine certains des problèmes qui peuvent être rencontrés lorsque vous travaillez avec d'autres bibliothèques.
Pour travailler pleinement avec des parties fractionnaires d'une seconde, vous devez utiliser le module DateTime . Sous certaines réserves, il vous permet d'effectuer toutes les opérations listées ci-dessus:
Le point non évident lors de l'utilisation de `DateTimeImmutable :: createFromFormat`La lettre u dans la chaîne de format signifie microsecondes, mais elle fonctionne également correctement dans le cas de parties fractionnaires de moindre précision. De plus, c'est le seul moyen de spécifier des parties fractionnaires d'une seconde dans une chaîne de format. Un exemple:
$time = \DateTimeImmutable::createFromFormat('Ymd H:i:s.u', '2019-09-12 21:32:43.9085');
Le principal problème de ce module est l'inconvénient de travailler avec des intervalles contenant des fractions de seconde (voire l'impossibilité d'un tel travail). La \DateInterval bien qu'elle contienne la partie fractionnaire d'une seconde précise aux mêmes six décimales, vous ne pouvez initialiser cette partie fractionnaire que par DateTime::diff . Le constructeur de la classe DateInterval et la méthode d'usine \DateInterval::createFromDateString ne peuvent fonctionner qu'avec des secondes entières et ne permettent pas de spécifier la partie fractionnaire:
Un autre problème peut survenir lors du calcul de la différence entre deux points dans le temps à l'aide de la méthode \DateTimeImmutable::diff . En PHP avant la version 7.2.12, il y avait un bug à cause duquel les parties fractionnaires d'une seconde existaient séparément des autres chiffres et pouvaient recevoir leur propre signe:
$timeBefore = new \DateTimeImmutable('2019-09-12 21:20:19.987654'); $timeAfter = new \DateTimeImmutable('2019-09-14 12:13:14.123456'); $diff = $timeBefore->diff($timeAfter); print $diff->format('%R%a days %H:%I:%S.%F') . PHP_EOL;
En général, je vous conseille d'être prudent lorsque vous travaillez avec des intervalles et de couvrir soigneusement ce code avec des tests.
Voir aussi:
Travailler avec un temps de haute précision dans PDO
PDO et mysqli sont les deux principales interfaces pour interroger les bases de données MySQL à partir du code PHP. Dans le contexte d'une conversation sur le temps, ils sont similaires les uns aux autres, donc je ne parlerai que de l'un d'eux - AOP.
Lorsque vous travaillez avec des bases de données dans PDO, l'heure apparaît à deux endroits:
- en tant que paramètre passé aux requêtes exécutées;
- comme une valeur qui vient en réponse aux requêtes SELECT.
Il est recommandé de passer des espaces réservés lors du passage de paramètres à la demande. Les espaces réservés peuvent transférer des valeurs à partir d'un très petit ensemble de types: valeurs booléennes, chaînes et entiers. Il n'y a pas de type approprié pour la date et l'heure, vous devez donc convertir manuellement la valeur d'un objet de la classe DateTime / DateTimeImmutable en une chaîne.
$now = new \DateTimeImmutable(); $db = new \PDO('mysql:...', 'user', 'password', [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]); $stmt = $db->prepare('INSERT INTO Test.ChatContactsList (title, last_message_send_time) VALUES (:title, :date)'); $result = $stmt->execute([':title' => "Test #1", ':date' => $now->format('Ymd H:i:s.u')]);
L'utilisation d'un tel code n'est pas très pratique, car à chaque fois vous devez formater la valeur transmise. Par conséquent, dans la base de code Badoo, nous avons implémenté la prise en charge des espaces réservés typés dans notre wrapper pour travailler avec la base de données. Dans le cas des dates, cela est très pratique, car il vous permet de transférer une valeur dans différents formats (un objet qui implémente DateTimeInterface, une chaîne formatée ou un nombre avec un horodatage), et toutes les transformations et vérifications nécessaires de l'exactitude des valeurs transférées sont déjà effectuées à l'intérieur. En prime, lors du transfert d'une valeur incorrecte, nous apprenons l'erreur immédiatement, et non après avoir reçu une erreur de MySQL lors de l'exécution de la requête.
La récupération de données à partir des résultats de requête semble assez simple. Lors de l'exécution de cette opération, PDO renvoie les données sous forme de chaînes, et dans le code, nous devons continuer à traiter les résultats si nous voulons travailler avec des objets temporels (et ici, nous avons besoin de la fonctionnalité pour obtenir l'heure de la chaîne formatée, dont j'ai parlé dans la section précédente).
$stmt = $db->prepare('SELECT * FROM Test.ChatContactsList ORDER BY last_message_send_time DESC, chat_id DESC LIMIT 5'); $stmt->execute(); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $row['last_message_send_time'] = is_null($row['last_message_send_time']) ? null : new \DateTimeImmutable($row['last_message_send_time']);
Remarque
Le fait que PDO renvoie des données sous forme de chaînes n'est pas entièrement vrai. Lors de la réception de valeurs, il est possible de définir le type de valeur pour la colonne à l'aide de la PDOStatement::bindColumn . Je n'en ai pas parlé car il y a le même ensemble limité de types qui n'aide pas avec les dates.
Malheureusement, il y a un problème à prendre en compte. En PHP avant la version 7.3, il y a un bogue à cause duquel PDO, lorsque l'attribut PDO::ATTR_EMULATE_PREPARES est PDO::ATTR_EMULATE_PREPARES "coupe" la partie fractionnaire d'une seconde lorsqu'il est reçu de la base de données. Des détails et un exemple peuvent être trouvés dans la description du bogue sur php.net . En PHP 7.3, cette erreur a été corrigée et a averti que ce changement rompt la compatibilité descendante .
Si vous utilisez PHP version 7.2 ou antérieure et n'êtes pas en mesure de le mettre à jour ou d'activer PDO::ATTR_EMULATE_PREPARES , vous pouvez contourner ce bogue en corrigeant les requêtes SQL qui renvoient l'heure avec une partie fractionnaire afin que cette colonne ait un type de chaîne. Cela peut être fait, par exemple, comme ceci:
SELECT *, CAST(last_message_send_time AS CHAR) AS last_message_send_time_fixed FROM ChatContactsList ORDER BY last_message_send_time DESC LIMIT 1;
Ce problème peut également être rencontré lorsque vous travaillez avec le module mysqli : si vous utilisez des requêtes préparées via un appel à la méthode mysqli::prepare , puis en PHP avant la version 7.3, la partie fractionnaire d'une seconde ne sera pas retournée. Comme avec PDO, vous pouvez résoudre ce problème en mettant à jour PHP ou en contournant la conversion temporelle en type de chaîne.
Voir aussi:
Travailler avec un temps de haute précision dans Yii 2
La plupart des frameworks modernes fournissent une fonctionnalité de migration qui vous permet de stocker l'historique des modifications de schéma de base de données dans le code et de le modifier de manière incrémentielle. Si vous utilisez des migrations et souhaitez utiliser du temps de haute précision, votre framework doit le prendre en charge. Heureusement, cela fonctionne dès le départ dans tous les principaux cadres.
Dans cette section, je montrerai comment ce support est implémenté dans Yii (dans les exemples j'ai utilisé la version 2.0.26). À propos de Laravel, Symfony et d'autres, je n'écrirai pas afin de ne pas rendre l'article sans fin, mais je serai heureux si vous ajoutez des détails dans les commentaires ou les nouveaux articles sur ce sujet.
Dans la migration, nous écrivons du code qui décrit les modifications apportées au schéma de données. Lors de la création d'une nouvelle table, nous décrivons toutes ses colonnes à l'aide de méthodes spéciales de la classe \ yii \ db \ Migration (elles sont déclarées dans la barre SchemaBuilderTrait ). Les méthodes time , timestamp et datetime qui peuvent prendre une valeur d'entrée de précision sont responsables de la description des colonnes contenant la date et l'heure.
Exemple de migration dans laquelle une nouvelle table est créée avec une colonne de temps de haute précision:
use yii\db\Migration; class m190914_141123_create_news_table extends Migration { public function up() { $this->createTable('news', [ 'id' => $this->primaryKey(), 'title' => $this->string()->notNull(), 'content' => $this->text(), 'published' => $this->timestamp(6),
Et voici un exemple de migration dans lequel la précision d'une colonne existante change:
class m190916_045702_change_news_time_precision extends Migration { public function up() { $this->alterColumn( 'news', 'published', $this->timestamp(6) ); return true; } public function down() { $this->alterColumn( 'news', 'published', $this->timestamp(3) ); return true; } }
ActiveRecord - : , DateTime-. , — «» PDO::ATTR_EMULATE_PREPARES . Yii , . , , PDO.
. :
Conclusion
, , — , . , , . , !