Una vez que me sorprendí pensando que cuando trabajo con el tiempo en las bases de datos, casi siempre uso el tiempo exacto al segundo simplemente porque estoy acostumbrado y que esta es la opción descrita en la documentación y una gran cantidad de ejemplos. Sin embargo, ahora esta precisión está lejos de ser suficiente para todas las tareas. Los sistemas modernos son complejos, pueden constar de muchas partes, tienen millones de usuarios que interactúan con ellos, y en muchos casos es más conveniente utilizar una mayor precisión, cuyo soporte ha existido durante mucho tiempo.
En este artículo hablaré sobre formas de usar el tiempo con partes fraccionarias de un segundo en MySQL y PHP. Fue concebido como un tutorial, por lo que el material está diseñado para una amplia gama de lectores y en algunos lugares repite la documentación. El valor principal debe ser que recopilé en un texto todo lo que necesita saber para trabajar con ese tiempo en MySQL, PHP y el marco Yii, y también agregué descripciones de problemas no obvios que puede encontrar.
Usaré el término "tiempo de alta precisión". En la documentación de MySQL verá el término "segundos fraccionarios", pero su traducción literal suena extraña, pero no encontré otra traducción establecida.
¿Cuándo usar el tiempo de alta precisión?
Para empezar, mostraré una captura de pantalla de la bandeja de entrada de mi bandeja de entrada que ilustra bien la idea:
Las letras son la reacción de la misma persona a un evento. Un hombre presionó accidentalmente el botón equivocado, rápidamente se dio cuenta de esto y se corrigió. Como resultado, recibimos dos cartas enviadas aproximadamente al mismo tiempo, que son importantes para ordenar correctamente. Si el tiempo de envío es el mismo, existe la posibilidad de que las letras se muestren en el orden incorrecto y el destinatario se avergüence, porque entonces recibirá el resultado incorrecto por el que contará.
Encontré las siguientes situaciones en las que el tiempo de alta precisión sería relevante:
- Desea medir el tiempo entre algunas operaciones. Aquí todo es muy simple: cuanto mayor sea la precisión de las marcas de tiempo en los límites del intervalo, mayor será la precisión del resultado. Si usa segundos completos, puede cometer un error durante 1 segundo (si cae en los bordes de segundos). Si usa seis decimales, entonces el error será seis órdenes de magnitud más bajo.
- Tiene una colección donde es probable que haya varios objetos con el mismo tiempo de creación. Un ejemplo es un chat familiar para todos, donde la lista de contactos se ordena por la hora del último mensaje. Si aparece la navegación página por página, incluso existe el riesgo de perder contactos en los bordes de la página. Este problema se puede resolver sin un tiempo de alta precisión debido a la clasificación y paginación por un par de campos (tiempo + identificador único de un objeto), pero esta solución tiene sus inconvenientes (al menos la complicación de las consultas SQL, pero no solo eso). Aumentar la precisión del tiempo ayudará a reducir la probabilidad de problemas y sin complicar el sistema.
- Necesita mantener un historial de cambios de algún objeto. Esto es especialmente importante en el mundo del servicio, donde las modificaciones pueden ocurrir en paralelo y en lugares completamente diferentes. Como ejemplo, puedo trabajar con fotografías de nuestros usuarios, donde se pueden realizar muchas operaciones diferentes en paralelo (el usuario puede hacer que la foto sea privada o eliminarla, se puede moderar en uno de varios sistemas, recortar para usar como una foto en el chat, etc.). )
Debe tenerse en cuenta que uno no puede creer los valores obtenidos en un 100% y la precisión real de los valores obtenidos puede ser inferior a seis decimales. Esto se debe al hecho de que podemos obtener un valor de tiempo inexacto (especialmente cuando trabajamos en un sistema distribuido que consta de muchos servidores), el tiempo puede cambiar inesperadamente (por ejemplo, al sincronizar a través de NTP o al cambiar el reloj), etc. No me detendré en todos estos problemas, pero le daré un par de artículos donde puede leer más sobre ellos:
Trabaja con tiempo de alta precisión en MySQL
MySQL admite tres tipos de columnas en las que se puede almacenar el tiempo: TIME , DATETIME y TIMESTAMP . Inicialmente, solo podían almacenar valores que eran múltiplos de un segundo (por ejemplo, 2019-08-14 19:20:21). En la versión 5.6.4, que se lanzó en diciembre de 2011, se hizo posible trabajar con la fracción de segundo. Para hacer esto, al crear la columna, debe especificar el número de lugares decimales, que deben almacenarse en la parte fraccionaria de la marca de tiempo. El número máximo de caracteres admitidos es seis, lo que le permite almacenar un tiempo preciso al microsegundo. Si intentas usar más caracteres, obtienes un error.
Un ejemplo:
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; +
En este ejemplo, la marca de tiempo del registro insertado tiene una fracción cero. Esto sucedió porque el valor de entrada se indicó al segundo más cercano. Para resolver el problema, la precisión del valor de entrada debe ser la misma que el valor en la base de datos. El consejo parece obvio, pero es relevante, ya que puede surgir un problema similar en aplicaciones reales: nos enfrentamos a una situación en la que el valor de entrada tenía tres decimales y seis estaban almacenados en la base de datos.
La forma más fácil de evitar que ocurra este problema es utilizar los valores de entrada con la máxima precisión (hasta microsegundos). En este caso, al escribir datos en la tabla, el tiempo se redondeará a la precisión requerida. Esta es una situación absolutamente normal que no causará ninguna advertencia (s):
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; +
Cuando se utiliza la inicialización automática y la actualización automática de columnas TIMESTAMP utilizando una estructura del formulario DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP es importante que los valores tengan la misma precisión que la columna misma:
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; +
Las funciones de MySQL para trabajar con el tiempo permiten trabajar con la parte fraccionaria de unidades de medida. No los enumeraré a todos (sugiero buscar en la documentación), pero daré algunos ejemplos:
Test> SELECT NOW(2), NOW(4), NOW(4) + INTERVAL 7.5 SECOND; +
El principal problema asociado con el uso de la parte fraccional de segundos en las consultas SQL es la inconsistencia de precisión en las comparaciones ( > , < , BETWEEN ). Puede encontrarlo si los datos en la base de datos tienen una precisión y en las consultas, otra. Aquí hay un pequeño ejemplo que ilustra este problema:
En este ejemplo, la precisión de los valores en la consulta es mayor que la precisión de los valores en la base de datos, y el problema ocurre "en el borde desde arriba". En la situación opuesta (si el valor de entrada tiene una precisión menor que el valor en la base de datos) no habrá problema: MySQL llevará el valor a la precisión deseada tanto en INSERT como en 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 consistencia de la precisión de los valores siempre debe tenerse en cuenta cuando se trabaja con tiempos de alta precisión. Si tales problemas de límites son críticos para usted, entonces debe asegurarse de que el código y la base de datos funcionen con el mismo número de decimales.
Reflexiones sobre cómo elegir la precisión en columnas con partes fraccionarias de segundosLa cantidad de espacio ocupado por la parte fraccionaria de una unidad de tiempo depende del número de caracteres en la columna. Parece natural elegir significados familiares: tres o seis decimales. Pero en el caso de tres personajes, no es tan simple. De hecho, MySQL usa un byte para almacenar dos lugares decimales:
Requisitos de almacenamiento de tipo de fecha y hora
Resulta que si selecciona tres lugares decimales, entonces no está utilizando completamente el espacio ocupado y para la misma sobrecarga podría tomar cuatro caracteres. En general, le recomiendo que siempre use un número par de caracteres y, si es necesario, "recorte" los innecesarios al imprimir. La opción ideal es no ser codicioso y tomar seis decimales. En el peor de los casos (con el tipo DATETIME), esta columna ocupará 8 bytes, es decir, el mismo número entero en la columna BIGINT.
Ver también:
Trabaja con tiempo de alta precisión en PHP
No es suficiente tener un tiempo de alta precisión en la base de datos; debe poder trabajar con él en el código de sus programas. En esta sección hablaré sobre tres puntos principales:
- Tiempo de recepción y formateo: explicaré cómo obtener la marca de tiempo antes de colocarla en la base de datos, obtenerla desde allí y hacer algún tipo de manipulación.
- Trabajar con tiempo en PDO: le mostraré un ejemplo de cómo PHP admite el formato de tiempo en una biblioteca de base de datos.
- Trabajar con el tiempo en marcos: hablaré sobre el uso del tiempo en las migraciones para cambiar la estructura de la base de datos.
Al trabajar con tiempo, hay varias operaciones básicas que debe poder realizar:
- obtener el punto actual en el tiempo;
- obteniendo un momento en el tiempo de alguna cadena formateada;
- agregar un período a un punto en el tiempo (o restar un período);
- obteniendo una cadena formateada por un punto en el tiempo.
En esta parte, te diré qué posibilidades hay de realizar estas operaciones en PHP.
La primera forma es trabajar con una marca de tiempo como número . En este caso, en el código PHP trabajamos con variables numéricas, que operamos a través de funciones como time , date , tiempo de strtotime . Este método no puede utilizarse para trabajar con tiempo de alta precisión, ya que en todas estas funciones las marcas de tiempo son un número entero (lo que significa que se perderá la parte fraccionaria en ellas).
Estas son las firmas de las principales funciones de la documentación oficial:
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 punto interesante sobre la función de fechaAunque es imposible pasar la parte fraccional de un segundo a la entrada de estas funciones, en la línea de la plantilla de formato pasada a la entrada de la función de date , puede configurar caracteres para mostrar milisegundos y microsegundos. Al formatear, los ceros siempre se devolverán en su lugar.
Un ejemplo:
$now = time(); print date('Ymd H:i:s.u', $now);
Además, este método incluye las hrtime microtime y hrtime , que le permiten obtener una hrtime tiempo con una parte fraccional para el momento actual. El problema es que no hay una forma lista para formatear una etiqueta y obtenerla de una cadena de un formato específico. Esto se puede resolver implementando independientemente estas funciones, pero no consideraré esa opción.
Si necesita trabajar solo con temporizadores, la biblioteca HRTime es una buena opción, que no consideraré con más detalle debido a las limitaciones de su uso. Solo puedo decir que le permite trabajar con tiempo hasta el nanosegundo y garantiza la monotonía de los temporizadores, lo que elimina algunos de los problemas que pueden surgir al trabajar con otras bibliotecas.
Para trabajar completamente con partes fraccionarias de un segundo, debe usar el módulo DateTime . Con ciertas reservas, le permite realizar todas las operaciones enumeradas anteriormente:
El punto no obvio cuando se usa `DateTimeImmutable :: createFromFormat`La letra u en la cadena de formato significa microsegundos, pero también funciona correctamente en el caso de partes fraccionarias de menor precisión. Además, esta es la única forma de especificar partes fraccionarias de un segundo en una cadena de formato. Un ejemplo:
$time = \DateTimeImmutable::createFromFormat('Ymd H:i:s.u', '2019-09-12 21:32:43.9085');
El principal problema de este módulo es el inconveniente al trabajar con intervalos que contienen segundos fraccionarios (o incluso la imposibilidad de dicho trabajo). La \DateInterval aunque contiene la parte fraccionaria de un segundo exacto a los mismos seis decimales, solo puede inicializar esta parte fraccional a través de DateTime::diff . El constructor de la clase DateInterval y el método de fábrica \DateInterval::createFromDateString solo pueden funcionar con segundos completos y no permiten especificar la parte fraccional:
Otro problema puede surgir al calcular la diferencia entre dos puntos en el tiempo usando el método \DateTimeImmutable::diff . En PHP anterior a la versión 7.2.12, había un error debido al cual las partes fraccionarias de un segundo existían por separado de otros dígitos y podían recibir su propio signo:
$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 general, le aconsejo que tenga cuidado al trabajar con intervalos y cubra cuidadosamente dicho código con pruebas.
Ver también:
Trabaja con tiempo de alta precisión en DOP
PDO y mysqli son las dos interfaces principales para consultar bases de datos MySQL desde código PHP. En el contexto de una conversación sobre el tiempo, son similares entre sí, por lo que solo hablaré de uno de ellos: DOP.
Cuando se trabaja con bases de datos en PDO, el tiempo aparece en dos lugares:
- como un parámetro pasado a consultas ejecutadas;
- como un valor que viene en respuesta a consultas SELECT.
Es una buena práctica pasar marcadores de posición al pasar parámetros a la solicitud. Los marcadores de posición pueden transferir valores desde un conjunto muy pequeño de tipos: valores booleanos, cadenas y enteros. No hay un tipo adecuado para la fecha y la hora, por lo que debe convertir manualmente el valor de un objeto de la clase DateTime / DateTimeImmutable en una cadena.
$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')]);
Usar dicho código no es muy conveniente, ya que cada vez que necesita formatear el valor transmitido. Por lo tanto, en la base de código de Badoo, implementamos soporte para marcadores de posición escritos en nuestro contenedor para trabajar con la base de datos. En el caso de las fechas, esto es muy conveniente, ya que le permite transferir un valor en diferentes formatos (un objeto que implementa DateTimeInterface, una cadena formateada o un número con una marca de tiempo), y todas las transformaciones y comprobaciones necesarias de la corrección de los valores transferidos ya se realizan dentro. Como beneficio adicional, al transferir un valor incorrecto, aprendemos sobre el error inmediatamente, y no después de recibir un error de MySQL al ejecutar la consulta.
Recuperar datos de los resultados de la consulta parece bastante simple. Al realizar esta operación, PDO devuelve los datos en forma de cadenas, y en el código necesitamos procesar aún más los resultados si queremos trabajar con objetos de tiempo (y aquí necesitamos la funcionalidad para obtener el tiempo de la cadena formateada, de la que hablé en la sección anterior).
$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']);
Nota
El hecho de que PDO devuelva datos como cadenas no es del todo cierto. Al recibir valores, es posible establecer el tipo de valor para la columna utilizando el PDOStatement::bindColumn . No hablé sobre esto porque hay el mismo conjunto limitado de tipos que no ayuda con las fechas.
Desafortunadamente, hay un problema a tener en cuenta. En PHP anterior a la versión 7.3, hay un error debido al cual PDO, cuando el atributo PDO::ATTR_EMULATE_PREPARES está PDO::ATTR_EMULATE_PREPARES "corta" la parte fraccional de un segundo cuando se recibe de la base de datos. Se pueden encontrar detalles y un ejemplo en la descripción del error en php.net . En PHP 7.3, este error se corrigió y advirtió que este cambio rompe la compatibilidad con versiones anteriores .
Si está utilizando PHP versión 7.2 o anterior y no puede actualizarlo o habilitar PDO::ATTR_EMULATE_PREPARES , puede PDO::ATTR_EMULATE_PREPARES este error corrigiendo las consultas SQL que devuelven el tiempo con una parte fraccionaria para que esta columna tenga un tipo de cadena. Esto se puede hacer, por ejemplo, así:
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;
Este problema también se puede encontrar al trabajar con el módulo mysqli : si usa consultas preparadas a través de una llamada al método mysqli::prepare , entonces en PHP anterior a la versión 7.3, no se devolverá la parte fraccional de un segundo. Al igual que con PDO, puede solucionar esto actualizando PHP o evitando la conversión de tiempo a un tipo de cadena.
Ver también:
Trabaja con tiempo de alta precisión en Yii 2
La mayoría de los marcos modernos proporcionan una funcionalidad de migración que le permite almacenar el historial de cambios en el esquema de la base de datos en el código y cambiarlo gradualmente. Si usa migraciones y desea usar un tiempo de alta precisión, entonces su marco debería admitirlo. Afortunadamente, esto funciona fuera de la caja en todos los marcos principales.
En esta sección, mostraré cómo se implementa este soporte en Yii (en los ejemplos utilicé la versión 2.0.26). Sobre Laravel, Symfony y otros, no escribiré para no hacer que el artículo sea interminable, pero me alegrará si agrega detalles en los comentarios o nuevos artículos sobre este tema.
En la migración, escribimos código que describe los cambios en el esquema de datos. Al crear una nueva tabla, describimos todas sus columnas utilizando métodos especiales de la clase \ yii \ db \ Migration (se declaran en la bandeja SchemaBuilderTrait ). Para la descripción de las columnas que contienen la fecha y la hora, los métodos de datetime , datetime y datetime son responsables, lo que puede tomar un valor de entrada de precisión.
Un ejemplo de una migración en la que se crea una nueva tabla con una columna de tiempo de alta precisión:
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),
Y este es un ejemplo de migración en el que cambia la precisión en una columna existente:
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.
. :
Conclusión
, , — , . , , . , !