Время высокой точности: как работать с долями секунды в MySQL и PHP08.10.2019 15:33

Однажды я поймал себя на мысли, что при работе со временем в базах данных почти всегда использую время с точностью до секунды просто потому, что я к этому привык и что именно такой вариант описан в документации и огромном количестве примеров. Однако сейчас такой точности достаточно далеко не для всех задач. Современные системы сложны — они могут состоять из множества частей, иметь миллионы пользователей, взаимодействующих с ними, — и во многих случаях удобнее использовать бОльшую точность, поддержка которой уже давно существует.

В этой статье я расскажу про способы использования времени с дробными частями секунды в MySQL и PHP. Она задумывалась как туториал, поэтому материал рассчитан на широкий круг читателей и местами повторяет документацию. Основную ценность должно представлять то, что я собрал в одном тексте всё, что нужно знать для работы с таким временем в MySQL, PHP и фреймворке Yii, а также добавил описания неочевидных проблем, с которыми можно столкнуться.

Я буду использовать термин «время высокой точности». В документации MySQL вы увидите термин «fractional seconds», но его дословный перевод звучит странно, а другого устоявшегося перевода я не нашёл.

Когда стоит использовать время высокой точности?

Для затравки покажу скриншот списка входящих писем моего почтового ящика, который хорошо иллюстрирует идею:

Письма представляют собой реакцию одного и того же человека на одно событие. Человек случайно нажал не на ту кнопку, быстро понял это и исправился. В результате мы получили два письма, отправленных примерно в одно и то же время, которые важно правильно отсортировать. Если время отправки совпадает, есть шанс, что письма будут показаны в неправильном порядке и получатель будет сконфужен, так как потом получит не тот результат, на который будет рассчитывать.

Я сталкивался со следующими ситуациями, в которых время высокой точности было бы актуально:

1. Вы хотите замерить время между какими-то операциями. Тут всё очень просто: чем выше точность меток времени на границах интервала, тем выше точность результата. Если вы используете целые секунды, то можете ошибиться на 1 секунду (при попадании на границы секунд). Если же использовать шесть знаков после запятой, то ошибка будет на шесть порядков ниже.

   
   

2. У вас есть коллекция, где велика вероятность наличия нескольких объектов с одинаковым временем создания. Примером может служить знакомый всем чат, где список контактов отсортирован по времени последнего сообщения. Если там появляется постраничная навигация, то возникает даже риск потери контактов на границах страниц. Эту проблему можно решить и без времени высокой точности за счёт сортировки и постраничного разбиения по паре полей (время + уникальный идентификатор объекта), но у этого решения есть свои недостатки (как минимум усложнение SQL-запросов, но не только это). Увеличение точности времени поможет снизить вероятность появления проблем и обойтись без усложнения системы.

   
   

3. Вам нужно хранить историю изменений какого-то объекта. Особенно важно это в сервисном мире, где модификации могут происходить параллельно и в совершенно разных местах. В качестве примера могу привести работу с фотографиями наших пользователей, где параллельно может осуществляться множество разных операций (пользователь может сделать фотографию приватной или удалить её, она может модерироваться в одной из нескольких систем, обрезаться для использования в качестве фото в чате и т. д.).

   
   

Нужно иметь в виду, что нельзя верить полученным значениям на 100% и реальная точность получаемых значений может быть меньше шести знаков после запятой. Это происходит из-за того, что мы можем получить неточное значение времени (особенно при работе в распределённой системе, состоящей из многих серверов), время может неожиданно измениться (например, при синхронизации через NTP или при переводе часов) и т. д. Я не стану здесь останавливаться на всех этих проблемах, но приведу пару статей, где про них можно почитать подробнее:

Работа со временем высокой точности в MySQL

MySQL поддерживает три типа колонок, в которых можно хранить время: TIME, DATETIME и TIMESTAMP. Изначально в них можно было хранить только значения, кратные одной секунде (например, 2019–08–14 19:20:21). В версии 5.6.4, которая вышла в декабре 2011 года, появилась возможность работать и с дробной частью секунды. Для этого при создании колонки нужно указать количество знаков после запятой, которое необходимо хранить в дробной части метки времени. Максимальное количество знаков, которое поддерживается, — шесть, что позволяет хранить время с точностью до микросекунды. При попытке использовать большее количество знаков вы получите ошибку.

Пример:

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;
+---------+---------+-------------------------+
| chat_id | title   | last_message_send_time  |
+---------+---------+-------------------------+
|       1 | Chat #1 | 2019-09-22 22:23:15.000 |
+---------+---------+-------------------------+
1 row in set (0.00 sec)

В этом примере у метки времени вставленной записи нулевая дробная часть. Это произошло потому, что входящее значение было указано с точностью до секунды. Для решения проблемы нужно, чтобы точность входящего значения была такой же, как у значения в базе данных. Совет кажется очевидным, но он актуален, поскольку подобная проблема может всплыть в реальных приложениях: мы сталкивались с ситуацией, когда значение на входе имело три знака после запятой, а в базе данных хранилось шесть.

Самый простой способ предупредить возникновение этой проблемы — использовать входящие значения с максимальной точностью (до микросекунды). В этом случае при записи данных в таблицу время округлится до требуемой точности. Это абсолютно нормальная ситуация, которая не будет вызывать никаких warning-ов (предупреждений):

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;
+---------+---------+-------------------------+
| chat_id | title   | last_message_send_time  |
+---------+---------+-------------------------+
|       1 | Chat #1 | 2019-09-22 22:23:15.235 |
+---------+---------+-------------------------+
1 row in set (0.00 sec)

При использовании автоматической инициализации и автоматического обновления колонок типа TIMESTAMP с помощью конструкции вида DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP важно, чтобы значения имели ту же точность, что и сама колонка:

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;
+---------+---------+-------------------------+-------------------------+
| chat_id | title   | last_message_send_time  | updated                 |
+---------+---------+-------------------------+-------------------------+
|       1 | Chat #1 | 2019-09-22 22:22:22.000 | 2019-09-22 22:26:39.968 |
+---------+---------+-------------------------+-------------------------+
1 row in set (0.00 sec)

Функции MySQL для работы со временем поддерживают работу и с дробной частью единиц измерения. Перечислять их все я не буду (предлагаю посмотреть в документации), но приведу несколько примеров:

Test> SELECT NOW(2), NOW(4), NOW(4) + INTERVAL 7.5 SECOND;
+------------------------+--------------------------+------------------------------+
| NOW(2)                 | NOW(4)                   | NOW(4) + INTERVAL 7.5 SECOND |
+------------------------+--------------------------+------------------------------+
| 2019-09-22 21:12:23.31 | 2019-09-22 21:12:23.3194 | 2019-09-22 21:12:30.8194     |
+------------------------+--------------------------+------------------------------+
1 row in set (0.00 sec)

Test> SELECT SUBTIME(CURRENT_TIME(6), CURRENT_TIME(3)), CURRENT_TIME(6), CURRENT_TIME(3);
+-------------------------------------------+-----------------+-----------------+
| SUBTIME(CURRENT_TIME(6), CURRENT_TIME(3)) | CURRENT_TIME(6) | CURRENT_TIME(3) |
+-------------------------------------------+-----------------+-----------------+
| 00:00:00.000712                           | 21:12:50.793712 | 21:12:50.793    |
+-------------------------------------------+-----------------+-----------------+
1 row in set (0.00 sec)

Главная проблема, с которой сопряжено использование дробной части секунд в SQL-запросах, — несогласованность точности при сравнениях (>, <, BETWEEN). С ней можно столкнуться в том случае, если данные в базе имеют одну точность, а в запросах — другую. Вот небольшой пример, иллюстрирующий эту проблему:

# На вход подаются шесть знаков в дробной части
Test> INSERT INTO ChatContactsList (title, last_message_send_time) VALUES ('Chat #2', '2019-09-22 21:16:39.123456');
Query OK, 0 row affected (0.00 sec)

Test> SELECT chat_id, title, last_message_send_time FROM ChatContactsList WHERE title='Chat #2';
+---------+---------+-------------------------+
| chat_id | title   | last_message_send_time  |
+---------+---------+-------------------------+
|       2 | Chat #2 | 2019-09-22 21:16:39.123 | <- Сохраняются только три знака из-за точности, указанной в колонке 
+---------+---------+-------------------------+
1 row in set (0.00 sec)

Test> SELECT title, last_message_send_time FROM ChatContactsList WHERE last_message_send_time >= '2019-09-22 21:16:39.123456'; <- Это то же значение, что было в INSERT-е
+---------+-------------------------+
| title   | last_message_send_time  |
+---------+-------------------------+
| Chat #1 | 2019-09-22 22:22:22.000 | 
+---------+-------------------------+
1 row in set (0.00 sec) <- Chat #2 не найден из-за того, что точность в базе ниже, чем точность на входе

В данном примере точность значений в запросе выше, чем точность значений в базе, и проблема возникает «на границе сверху». В обратной ситуации (если значение на входе будет иметь точность ниже, чем значение в базе) проблемы не будет — MySQL приведёт значение к нужной точности и в INSERT-е, и в 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';
+---------+-------------------------+
| title   | last_message_send_time  |
+---------+-------------------------+
| Chat #3 | 2019-09-03 21:20:19.100 |
+---------+-------------------------+
1 row in set (0.00 sec)

Согласованность точности значений всегда стоит держать в голове при работе со временем высокой точности. Если подобные граничные проблемы для вас критичны, то нужно следить за тем, чтобы код и база данных работали с одинаковым количеством знаков после запятой.

См. также:

Работа со временем высокой точности в PHP

Мало иметь время высокой точности в базе данных — нужно уметь работать с ним в коде ваших программ. В этом разделе я расскажу про три основных момента:

1. Получение и форматирование времени: объясню, как получить метку времени перед тем, как положить его в базу данных, получить его оттуда и осуществить какие-то манипуляции.
2. Работа со временем в PDO: покажу на примере, как PHP поддерживает форматирование времени в библиотеке по работе с базой данных.
3. Работа со временем во фреймворках: расскажу про использование времени в миграциях для изменения структуры базы данных.

Получение и форматирование времени

При работе со временем есть несколько основных операций, которые нужно уметь делать:

• получение текущего момента времени;
• получение момента времени из какой-то отформатированной строки;
• добавление к моменту времени какого-то периода (или вычитание периода);
• получение форматированной строки для момента времени.

В этой части я расскажу, какие возможности для выполнения этих операций есть в PHP.

Первый способ — это работа с меткой времени как с числом. В этом случае в PHP-коде мы работаем с численными переменным, которыми оперируем через такие функции, как time, date, strtotime. Этот способ нельзя использовать для работы со временем высокой точности, поскольку во всех этих функциях метки времени представляют собой целое число (а значит, дробная часть в них будет потеряна).

Вот сигнатуры основных таких функций из официальной документации:

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

$now = time();
print date('Y-m-d H:i:s.u', $now);
// 2019-09-11 21:27:18.000000

print date('Y-m-d H:i:s.v', $now);
// 2019-09-11 21:27:18.000

Также к этому способу можно отнести функции microtime и hrtime, которые позволяют получить метку времени с дробной частью для текущего момента. Проблема заключается в том, что нет готового способа форматирования такой метки и получения её из строки определённого формата. Это можно решить, самостоятельно реализовав эти функции, но я не буду рассматривать такой вариант.

Если вам нужно работать только с таймерами, то хорошим вариантом будет библиотека HRTime, которую я не стану рассматривать подробнее из-за ограниченности её применения. Скажу только, что она позволяет работать со временем с точностью до наносекунды и гарантирует монотонность таймеров, что избавляет от части проблем, с которыми можно столкнуться при работе с другими библиотеками.

Для полноценной работы с дробными частями секунды нужно использовать модуль DateTime. С определёнными оговорками он позволяет выполнять все перечисленные ранее операции:

// Получение текущего момента времени:
$time = new \DateTimeImmutable();

// Получение момента времени из отформатированной строки:
$time = new \DateTimeImmutable('2019-09-12 21:32:43.908502');
$time = \DateTimeImmutable::createFromFormat('Y-m-d H:i:s.u', '2019-09-12 21:32:43.9085');

// Добавление/вычитание периода:
$period = \DateInterval::createFromDateString('5 seconds');
$timeBefore = $time->add($period);
$timeAfter = $time->sub($period);

// Получение форматированной строки для момента времени:
print $time->format('Y-m-d H:i:s.v'); // '2019-09-12 21:32:43.908'
print $time->format("Y-m-d H:i:s.u"); // '2019-09-12 21:32:43.908502'

$time = \DateTimeImmutable::createFromFormat('Y-m-d H:i:s.u', '2019-09-12 21:32:43.9085');
// => Получаем объект DateTimeImmutable со значением времени 2019-09-12 21:32:43.908500

$time = \DateTimeImmutable::createFromFormat('Y-m-d H:i:s.u', '2019-09-12 21:32:43.90');
// => Получаем объект DateTimeImmutable со значением времени 2019-09-12 21:32:43.900000

$time = \DateTimeImmutable::createFromFormat('Y-m-d H:i:s.u', '2019-09-12 21:32:43');
// => Получаем false

Главной проблемой данного модуля является неудобство при работе с интервалами, содержащими дробные секунды (а то и невозможность такой работы). Класс \DateInterval хотя и содержит дробную часть секунды с точностью до тех же самых шести знаков после запятой, но инициализировать эту дробную часть можно только через DateTime::diff. Конструктор класса DateInterval и фабричный метод \DateInterval::createFromDateString умеют работать только с целыми секундами и не позволяют задать дробную часть:

// Эта строка выбросит исключение из-за некорректного формата
$buggyPeriod1 = new \DateInterval('PT7.500S');

// Эта строка отработает и вернёт объект периода, но без секунд
$buggyPeriod2 = \DateInterval::createFromDateString('2 minutes 7.5 seconds');
print $buggyPeriod2->format('%R%H:%I:%S.%F') . PHP_EOL;
// Выведет "+00:02:00.000000"

Другая проблема может появиться при вычислении разницы между двумя моментами времени с помощью метода \DateTimeImmutable::diff. В PHP до версии 7.2.12 был баг, из-за которого дробные части секунды существовали отдельно от других разрядов и могли получить свой собственный знак:

$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;
// В PHP версии 7.2.12+ результатом будет "+1 days 14:52:54.135802"
// В более ранних версиях мы получим "+1 days 14:52:55.-864198"

В целом я советую проявлять осторожность при работе с интервалами и тщательно покрывать такой код тестами.

См. также:

Работа со временем высокой точности в PDO

PDO и mysqli — это два основных интерфейса для выполнения запросов к базам данных MySQL из PHP-кода. В контексте разговора про время они похожи друг на друга, поэтому я расскажу только про один из них — PDO.

При работе с базами данных в PDO время фигурирует в двух местах:

• в качестве параметра, передаваемого в выполняемые запросы;
• в качестве значения, приходящего в ответ на SELECT-запросы.

Хорошим тоном при передаче параметров в запрос является использование плейсхолдеров. В плейсхолдеры можно передавать значения из очень небольшого набора типов: булевы значения, строки и целые числа. Подходящего типа для даты и времени нет, поэтому необходимо вручную преобразовать значение из объекта класса DateTime/DateTimeImmutable в строку.

$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('Y-m-d H:i:s.u')]);

Использовать такой код не очень удобно, поскольку каждый раз нужно делать форматирование переданного значения. Поэтому в кодовой базе Badoo мы реализовали поддержку типизированных плейсхолдеров в нашей обёртке для работы с базой данных. В случае с датами это очень удобно, так как позволяет передавать значение в разных форматах (объект, реализующий DateTimeInterface, отформатированная строка или число с меткой времени), а уже внутри делаются все необходимые преобразования и проверки корректности переданных значений. В качестве бонуса при передаче некорректного значения мы узнаём об ошибке сразу, а не после получения ошибки от MySQL при выполнении запроса.

Получение данных из результатов запроса выглядит довольно просто. При выполнении этой операции PDO отдаёт данные в виде строк, и в коде нужно дополнительно обработать результаты, если мы хотим работать с объектами времени (и тут нам потребуется функционал получения момента времени из отформатированной строки, о котором я рассказывал в предыдущем разделе).

$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']);
    // Делаем что-то полезное
}

Примечание

То, что PDO отдаёт данные в виде строк, — не совсем правда. При получении значений есть возможность задать тип значения для колонки с помощью метода PDOStatement::bindColumn. Я не стал рассказывать об этом из-за того, что там есть тот же ограниченный набор типов, который не поможет в случае с датами.

К сожалению, тут есть проблема, о которой нужно знать. В PHP до версии 7.3 есть баг, из-за которого PDO при выключенном атрибуте PDO::ATTR_EMULATE_PREPARES «обрезает» дробную часть секунды при её получении из базы. Подробности и пример можно посмотреть в описании бага на php.net. В PHP 7.3 эту ошибку исправили и предупредили о том, что это изменение ломает обратную совместимость.

Если вы используете PHP версии 7.2 или старше и не имеете возможности обновить её или включить PDO::ATTR_EMULATE_PREPARES, то вы можете обойти этот баг, поправив SQL-запросы, возвращающие время с дробной частью, так, чтобы эта колонка имела строковый тип. Это можно сделать, например, так:

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;

С этой проблемой можно столкнуться и при работе с модулем mysqli: если вы используете подготовленные запросы через вызов метода mysqli::prepare, то в PHP до версии 7.3 дробная часть секунды не будет возвращаться. Как и в случае с PDO, можно исправить это, обновив PHP, или обойти через преобразование времени к строковому типу.

См. также:

Работа со временем высокой точности в Yii 2

Большинство современных фреймворков предоставляют функционал миграций, который позволяет хранить в коде историю изменений схемы базы данных и инкрементально изменять её. Если вы используете миграции и хотите использовать время высокой точности, то ваш фреймворк должен его поддерживать. К счастью, это работает из коробки во всех основных фреймворках.

В данном разделе я покажу, как эта поддержка реализована в Yii (в примерах я использовал версию 2.0.26). Про Laravel, Symfony и другие я не стану писать, чтобы не делать статью бесконечной, но буду рад, если вы добавите деталей в комментариях или новых статьях на эту тему.

В миграции мы пишем код, который описывает изменения в схеме данных. При создании новой таблицы мы описываем все её колонки с помощью специальных методов из класса \yii\db\Migration (они объявлены в трейте SchemaBuilderTrait). За описание колонок, содержащих дату и время, отвечают методы time, timestamp и datetime, которые могут принимать на вход значение точности.

Пример миграции, в которой создаётся новая таблица с колонкой времени высокой точности:

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), // шесть знаков после запятой
        ]);
    }

    public function down()
    {
        $this->dropTable('news');
    }
}

А это пример миграции, в которой меняется точность в уже существующей колонке:

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.

См. также:

Заключение

Надеюсь, мне удалось показать, что время высокой точности — полезная штука, которую можно использовать уже сегодня. Если после прочтения статьи вы станете более осознанно подходить к работе с точностью времени в своих проектах, то я буду считать, что добился своей цели. Спасибо, что дочитали до конца!

© Habrahabr.ru