Depois que me peguei pensando que, ao trabalhar com o tempo nos bancos de dados, quase sempre utilizo o tempo exato para o segundo simplesmente porque estou acostumado a isso e que essa é a opção descrita na documentação e em um grande número de exemplos. No entanto, agora essa precisão está longe de ser suficiente para todas as tarefas. Os sistemas modernos são complexos - eles podem consistir em várias partes, ter milhões de usuários interagindo com eles - e, em muitos casos, é mais conveniente usar maior precisão, cujo suporte já existe há muito tempo.
Neste artigo, falarei sobre maneiras de usar o tempo com partes fracionárias de segundo no MySQL e PHP. Como foi concebido como um tutorial, o material foi projetado para uma ampla gama de leitores e, em alguns lugares, repete a documentação. O principal valor deve ser que eu coletei em um texto tudo o que você precisa saber para trabalhar com esse tempo no MySQL, PHP e no framework Yii, além de adicionar descrições de problemas não óbvios que você pode encontrar.
Vou usar o termo "tempo de alta precisão". Na documentação do MySQL, você verá o termo "segundos fracionários", mas sua tradução literal parece estranha, mas não encontrei outra tradução estabelecida.
Quando usar o tempo de alta precisão?
Para começar, mostrarei uma captura de tela da caixa de entrada da minha caixa de entrada que ilustra bem a ideia:
As cartas são a reação da mesma pessoa a um evento. Um homem acidentalmente apertou o botão errado, rapidamente percebeu isso e se corrigiu. Como resultado, recebemos duas cartas enviadas ao mesmo tempo, que são importantes para classificar corretamente. Se o tempo de envio for o mesmo, há uma chance de que as letras sejam exibidas na ordem errada e o destinatário fique constrangido, porque ele receberá o resultado errado pelo qual contará.
Me deparei com as seguintes situações nas quais o tempo de alta precisão seria relevante:
- Você deseja medir o tempo entre algumas operações. Tudo é muito simples aqui: quanto maior a precisão dos registros de data e hora nos limites do intervalo, maior a precisão do resultado. Se você usar segundos inteiros, poderá cometer um erro por 1 segundo (se cair nas bordas dos segundos). Se você usar seis casas decimais, o erro será seis ordens de magnitude inferiores.
- Você tem uma coleção na qual é provável que haja vários objetos com o mesmo horário de criação. Um exemplo é um bate-papo familiar para todos, onde a lista de contatos é classificada pela hora da última mensagem. Se aparecer a navegação página por página, existe o risco de perder contatos nas bordas da página. Esse problema pode ser resolvido sem tempo de alta precisão devido à classificação e paginação por um par de campos (tempo + identificador exclusivo de um objeto), mas esta solução tem suas desvantagens (pelo menos a complicação das consultas SQL, mas não apenas isso). Aumentar a precisão do tempo ajudará a reduzir a probabilidade de problemas e ocorrer sem complicar o sistema.
- Você precisa manter um histórico de alterações de algum objeto. Isso é especialmente importante no mundo dos serviços, onde as modificações podem ocorrer em paralelo e em lugares completamente diferentes. Como exemplo, posso trabalhar com fotografias de nossos usuários, onde muitas operações diferentes podem ser realizadas em paralelo (o usuário pode tornar a foto privada ou excluí-la, ela pode ser moderada em um dos vários sistemas, cortada para uso como foto no chat, etc.). )
Deve-se ter em mente que não se pode acreditar nos valores obtidos em 100% e a precisão real dos valores obtidos pode ser menor que seis casas decimais. Isso se deve ao fato de podermos obter um valor de tempo impreciso (especialmente ao trabalhar em um sistema distribuído composto por muitos servidores), o tempo pode mudar inesperadamente (por exemplo, ao sincronizar via NTP ou ao alterar o relógio), etc. Não vou me debruçar sobre todos esses problemas, mas darei alguns artigos em que você pode ler mais sobre eles:
Trabalhe com tempo de alta precisão no MySQL
O MySQL suporta três tipos de colunas nas quais o tempo pode ser armazenado: TIME , DATETIME e TIMESTAMP . Inicialmente, eles só podiam armazenar valores múltiplos de um segundo (por exemplo, 14/08/2019 às 19:20:21). Na versão 5.6.4, lançada em dezembro de 2011, tornou-se possível trabalhar com a parte fracionária de um segundo. Para fazer isso, ao criar a coluna, você precisa especificar o número de casas decimais, que devem ser armazenadas na parte fracionária do registro de data e hora. O número máximo de caracteres com suporte é seis, o que permite armazenar o tempo exato no microssegundo. Se você tentar usar mais caracteres, receberá um erro.
Um exemplo:
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; +
Neste exemplo, o registro de data e hora do registro inserido tem uma fração zero. Isso aconteceu porque o valor de entrada foi indicado no segundo mais próximo. Para resolver o problema, a precisão do valor de entrada deve ser igual ao valor no banco de dados. O conselho parece óbvio, mas é relevante, pois um problema semelhante pode surgir em aplicativos reais: fomos confrontados com uma situação em que o valor de entrada tinha três casas decimais e seis eram armazenados no banco de dados.
A maneira mais fácil de impedir que esse problema ocorra é usar os valores de entrada com precisão máxima (até microssegundos). Nesse caso, ao gravar dados na tabela, o tempo será arredondado para a precisão necessária. Essa é uma situação absolutamente normal que não causará nenhum aviso:
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; +
Ao usar a inicialização automática e a atualização automática das colunas TIMESTAMP usando uma estrutura no formato DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP é importante que os valores tenham a mesma precisão que a própria coluna:
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; +
As funções do MySQL para trabalhar com o passar do tempo suportam o trabalho com a parte fracionária das unidades de medida. Não listarei todos (sugiro procurar na documentação), mas darei alguns exemplos:
Test> SELECT NOW(2), NOW(4), NOW(4) + INTERVAL 7.5 SECOND; +
O principal problema associado ao uso da parte fracionária de segundos nas consultas SQL é a inconsistência da precisão nas comparações ( > , < , BETWEEN ). Você pode encontrá-lo se os dados no banco de dados tiverem uma precisão e nas consultas - outra. Aqui está um pequeno exemplo que ilustra esse problema:
Neste exemplo, a precisão dos valores na consulta é maior que a precisão dos valores no banco de dados e o problema ocorre "na borda de cima". Na situação oposta (se o valor de entrada tiver uma precisão menor que o valor do banco de dados), não haverá problema - o MySQL trará o valor para a precisão desejada em INSERT e 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'; +
A consistência da precisão dos valores deve ser sempre lembrada ao trabalhar com tempo de alta precisão. Se esses problemas de limite forem críticos para você, verifique se o código e o banco de dados funcionam com o mesmo número de casas decimais.
Reflexões sobre a escolha da precisão em colunas com partes fracionárias de segundosA quantidade de espaço ocupado pela parte fracionária de uma unidade de tempo depende do número de caracteres na coluna. Parece natural escolher significados familiares: três ou seis casas decimais. Mas no caso de três caracteres, não é tão simples. De fato, o MySQL usa um byte para armazenar duas casas decimais:
Requisitos de armazenamento para data e hora
Acontece que, se você selecionar três casas decimais, não estará utilizando totalmente o espaço ocupado e, para a mesma sobrecarga, poderá usar quatro caracteres. Em geral, eu recomendo que você sempre use um número par de caracteres e, se necessário, “corte” caracteres desnecessários ao imprimir. A opção ideal é não ser ganancioso e usar seis casas decimais. Na pior das hipóteses (com o tipo DATETIME), esta coluna ocupará 8 bytes, ou seja, o mesmo que o número inteiro na coluna BIGINT.
Veja também:
Trabalhe com tempo de alta precisão em PHP
Não basta ter tempo de alta precisão no banco de dados - você precisa trabalhar com ele no código dos seus programas. Nesta seção, falarei sobre três pontos principais:
- Horário de recebimento e formatação: explicarei como obter o registro de data e hora antes de colocá-lo no banco de dados, obtê-lo de lá e fazer algum tipo de manipulação.
- Trabalhando com o tempo no DOP: mostrarei um exemplo de como o PHP suporta o tempo de formatação em uma biblioteca de banco de dados.
- Trabalhando com o tempo nas estruturas: falarei sobre o uso do tempo nas migrações para alterar a estrutura do banco de dados.
Ao trabalhar com o tempo, há várias operações básicas que você precisa ser capaz de fazer:
- obtendo o ponto atual no tempo;
- obtendo um momento no tempo de alguma string formatada;
- adicionando um período a um ponto no tempo (ou subtraindo um período);
- obtendo uma string formatada para um ponto no tempo.
Nesta parte, mostrarei quais são as possibilidades para executar essas operações no PHP.
A primeira maneira é trabalhar com um registro de data e hora como um número . Nesse caso, no código PHP, trabalhamos com variáveis numéricas, as quais operamos através de funções como time , date e strtotime . Este método não pode ser usado para trabalhar com tempo de alta precisão, pois em todas essas funções os carimbos de data / hora são um número inteiro (o que significa que a parte fracionária deles será perdida).
Aqui estão as assinaturas das principais funções desta documentação 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
Um ponto interessante sobre a função de dataEmbora seja impossível passar a parte fracionária de um segundo para a entrada dessas funções, na linha do modelo de formatação passada para a entrada da função de date , você pode definir caracteres para exibir milissegundos e microssegundos. Ao formatar, os zeros sempre serão retornados em seus lugares.
Um exemplo:
$now = time(); print date('Ymd H:i:s.u', $now);
Também incluem esse método as hrtime microtime e hrtime , que permitem obter um hrtime hora com uma parte fracionária para o momento atual. O problema é que não existe uma maneira pronta para formatar esse rótulo e obtê-lo a partir de uma sequência de um formato específico. Isso pode ser resolvido através da implementação independente dessas funções, mas não considerarei essa opção.
Se você precisar trabalhar apenas com cronômetros, a biblioteca HRTime é uma boa opção, que não considerarei mais detalhadamente devido às limitações de seu uso. Só posso dizer que isso permite que você trabalhe com o tempo até o nanossegundo e garante a monotonia dos timers, o que elimina alguns dos problemas que podem ser encontrados ao trabalhar com outras bibliotecas.
Para trabalhar totalmente com partes fracionárias de segundo, você precisa usar o módulo DateTime . Com certas reservas, ele permite executar todas as operações listadas acima:
O ponto não óbvio ao usar `DateTimeImmutable :: createFromFormat`A letra u na string de formato significa microssegundos, mas também funciona corretamente no caso de partes fracionárias de menor precisão. Além disso, esta é a única maneira de especificar partes fracionárias de um segundo em uma string de formato. Um exemplo:
$time = \DateTimeImmutable::createFromFormat('Ymd H:i:s.u', '2019-09-12 21:32:43.9085');
O principal problema deste módulo é o inconveniente ao trabalhar com intervalos contendo segundos fracionários (ou mesmo a impossibilidade de tal trabalho). A \DateInterval embora contenha a parte fracionária de um segundo com as mesmas seis casas decimais precisas, você só pode inicializar essa parte fracionária por meio de DateTime::diff . O construtor da classe DateInterval e o método de fábrica \DateInterval::createFromDateString podem funcionar apenas com segundos inteiros e não permitem especificar a parte fracionária:
Outro problema pode surgir ao calcular a diferença entre dois pontos no tempo usando o método \DateTimeImmutable::diff . No PHP anterior à versão 7.2.12, havia um erro devido ao qual as partes fracionárias de um segundo existiam separadamente de outros dígitos e podiam receber seu próprio sinal:
$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;
Em geral, aconselho que você tenha cuidado ao trabalhar com intervalos e cubra cuidadosamente esse código com testes.
Veja também:
Trabalhe com tempo de alta precisão no DOP
PDO e mysqli são as duas interfaces principais para consultar bancos de dados MySQL a partir do código PHP. No contexto de uma conversa sobre o tempo, eles são semelhantes um ao outro, então eu vou falar apenas sobre um deles - DOP.
Ao trabalhar com bancos de dados no DOP, a hora aparece em dois locais:
- como um parâmetro passado para consultas executadas;
- como um valor que vem em resposta às consultas SELECT.
É uma boa prática passar espaços reservados ao passar parâmetros para a solicitação. Os espaços reservados podem transferir valores de um conjunto muito pequeno de tipos: valores booleanos, seqüências de caracteres e números inteiros. Não há um tipo adequado para data e hora, portanto, você deve converter manualmente o valor de um objeto da classe DateTime / DateTimeImmutable em uma seqüência de caracteres.
$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 esse código não é muito conveniente, pois cada vez que você precisa formatar o valor transmitido. Portanto, na base de código do Badoo, implementamos o suporte para espaços reservados digitados em nosso wrapper para trabalhar com o banco de dados. No caso de datas, isso é muito conveniente, pois permite transferir um valor em diferentes formatos (um objeto que implementa DateTimeInterface, uma sequência de caracteres formatada ou um número com carimbo de data / hora) e todas as transformações e verificações necessárias da correção dos valores transferidos já são realizadas dentro. Como bônus, ao transferir um valor incorreto, aprendemos sobre o erro imediatamente, e não após receber um erro do MySQL ao executar a consulta.
A recuperação de dados dos resultados da consulta parece bastante simples. Ao executar esta operação, o PDO retorna os dados na forma de seqüências de caracteres, e no código precisamos processar mais os resultados se quisermos trabalhar com objetos de tempo (e aqui precisamos da funcionalidade para obter o tempo da sequência de caracteres formatada, sobre a qual falei na seção 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
O fato de o PDO retornar dados como seqüências de caracteres não é inteiramente verdade. Ao receber valores, é possível definir o tipo de valor para a coluna usando o PDOStatement::bindColumn . Eu não falei sobre isso porque há o mesmo conjunto limitado de tipos que não ajuda nas datas.
Infelizmente, há um problema para estar ciente. No PHP anterior à versão 7.3, há um erro devido ao qual o PDO, quando o atributo PDO::ATTR_EMULATE_PREPARES está PDO::ATTR_EMULATE_PREPARES "corta" a parte fracionária de um segundo quando é recebido do banco de dados. Detalhes e um exemplo podem ser encontrados na descrição do bug no php.net . No PHP 7.3, esse erro foi corrigido e avisou que essa alteração quebra a compatibilidade com versões anteriores .
Se você estiver usando o PHP versão 7.2 ou anterior e não puder atualizá-lo ou ativar o PDO::ATTR_EMULATE_PREPARES , poderá solucionar esse erro corrigindo consultas SQL que retornam o tempo com uma parte fracionária, para que esta coluna tenha um tipo de string. Isso pode ser feito, por exemplo, assim:
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 também pode ser encontrado ao trabalhar com o módulo mysqli : se você usar consultas preparadas chamando o método mysqli::prepare , no PHP anterior à versão 7.3, a parte fracionária de um segundo não será retornada. Assim como no PDO, você pode corrigir isso atualizando o PHP ou ignorando a conversão de tempo em um tipo de string.
Veja também:
Trabalhar com tempo de alta precisão no Yii 2
As estruturas mais modernas fornecem funcionalidade de migração que permite armazenar o histórico de alterações no esquema do banco de dados no código e alterá-lo de forma incremental. Se você usa migrações e deseja usar um tempo de alta precisão, sua estrutura deve suportá-lo. Felizmente, isso funciona imediatamente em todas as principais estruturas.
Nesta seção, mostrarei como esse suporte é implementado no Yii (nos exemplos que usei a versão 2.0.26). Sobre o Laravel, Symfony e outros, não escreverei para não tornar o artigo infinito, mas ficarei feliz em adicionar detalhes nos comentários ou novos artigos sobre este tópico.
Na migração, escrevemos código que descreve as alterações no esquema de dados. Ao criar uma nova tabela, descrevemos todas as suas colunas usando métodos especiais da classe \ yii \ db \ Migration (eles são declarados na bandeja SchemaBuilderTrait ). Os métodos de timestamp , timestamp e datetime e datetime e datetime que podem ter um valor de entrada de precisão são responsáveis pela descrição das colunas que contêm data e hora.
Um exemplo de migração em que uma nova tabela é criada com uma coluna de tempo de alta precisão:
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),
E este é um exemplo de migração em que a precisão em uma coluna existente é alterada:
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.
. :
Conclusão
, , — , . , , . , !