company_banner

Время высокой точности: как работать с долями секунды в MySQL и PHP

  • Tutorial


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


В этой статье я расскажу про способы использования времени с дробными частями секунды в 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)

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


Мысли о выборе точности в колонках с дробными частями секунд

Объём места, занимаемого дробной частью единицы времени, зависит от количества знаков в колонке. Кажется естественным выбирать привычные значения: три или шесть знаков после запятой. Но в случае с тремя знаками не всё так просто. Фактически MySQL использует один байт для хранения двух знаков дробной части:


Fractional Seconds Precision Storage Required
0 0 bytes
1, 2 1 byte
3, 4 2 bytes
5, 6 3 bytes


Date and Time Type Storage Requirements

Получается, что если вы выбираете три знака после запятой, то не в полной мере используете занятое место и при тех же накладных расходах могли бы взять четыре знака. Вообще я рекомендую всегда использовать чётное количество знаков и при необходимости «обрезать» ненужные при выводе. Идеальный же вариант — не жадничать и брать шесть знаков после запятой. В худшем случае (при типе DATETIME) эта колонка займёт 8 байт, то есть столько же, сколько целое число в колонке типа BIGINT.


См. также:



Работа со временем высокой точности в 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

Любопытный момент про функцию date

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


Символ в строке format Описание Пример возвращаемого значения
u Микросекунды (добавлено в PHP 5.2.2). Учтите, что date() всегда будет возвращать 000000, т.к. она принимает целочисленный параметр, тогда как DateTime::format() поддерживает микросекунды, если DateTime создан с ними. Например: 654321
v Миллисекунды (добавлено в PHP 7.0.0). Замечание такое же как и для u. Например: 654

Пример:


$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'

Неочевидный момент при использовании `DateTimeImmutable::createFromFormat`

Буква u в строке форматирования означает микросекунды, но она корректно работает и в случае с дробными частями меньшей точности. Более того, это единственный способ задать дробные части секунды в строке формата. Пример:


$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.


См. также:



Заключение


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

Only registered users can participate in poll. Log in, please.

А вы используете время высокой точности в своих проектах?

  • 5.4%Использую постоянно5
  • 26%Использую, но редко24
  • 20.6%Не использую, но планирую начать19
  • 47.8%Не использую и не планирую начинать44
Badoo
435.03
Big Dating
Share post

Comments 24

    +1
    Если там появляется постраничная навигация, то возникает даже риск потери контактов на границах страниц.

    Скажем с комментами может быть такая проблема. Если они отображаются в формате «10 последних на 1 странице», то листая до конца страницы №2 и более нужно будет обновить страницу и глянуть, не появилось ли что-то за время чтения. На примере samlib.ru.
      0

      Не совсем понял пример.


      В моём примере подразумевалось, что если мы делаем пагинацию без оффсетов (при использовании которых есть свои проблемы), а на базе условий вида WHERE created < "2019-10-08 12:13:14" ORDER BY created DESC LIMIT 10 (где 2019-10-08 12:13:14 — это время последнего объекта предыдущей страницы, а 10 — количество записей на страницу), то мы не покажем другие объекты с тем же значением created если они есть, но не попали на предыдущую страницу из-за лимита записей на эту самую страницу.

        +1
        Может пропасть несколько записей с временем создания 12:13:14<t<12:13:14.999, если последняя запись предыдущей страницы попала в этот диапазон (и время создания было конвертировано в 12:13:14)?
          0

          Да, все эти записи будут проигнорированы при выборке. Если же заменить условие created < X на created <= X, то сюда попадёт и та запись, что уже была показана на предыдущей странице.


          Эту проблему тоже можно решить — добавить к сортировке и фильтру по created второе поле, уникальное для каждой записи (тот же автоинкрементный id, например), но придётся усложнять условия выборки. Запрос на выборку страницы тогда будет выглядеть как-то так:


          SELECT id, created
          FROM CommentsTable
          WHERE ((created > :last_shown_created) OR (created = :last_shown_created AND id > :last_shown_id))
          ORDER BY created ASC, id ASC
          LIMIT 10

          Тут в условии (created = :last_shown_created AND id >:last_shown_id) отвечает за корректную обработку случая, когда есть несколько записей в то же время, что и у граничной записи, а (created > :last_shown_created) за обработку случая, когда их нет.


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


          Повышение точности значения времени — самый простой способ уменьшить вероятность появления записей с одним и тем же временем на границе страниц (но совсем избавиться от неё нельзя) без серьёзного изменения кода.


          Есть и другие способы для решения этой проблемы, но они не имеют отношения к времени и это уже совсем другая история...

            +2

            И все же. Привязка только ко времени не дает стопроцентной гарантии правильного порядка, на примере писем. Банально даже ntp клиент отработает и скорректирует время на те самые доли секунд и порядок писем уже будет неправильный. Мне кажется, что сортировка по автоинкременту вне конкуренции.

              0

              Да, вы правы — время может быть скорректировано и порядок будет неправильные на те самые доли секунды. Но этим примером я хотел проиллюстрировать пагинацию на основе значения времени. Несмотря на этот неправильный порядок, вызванный корректировкой времени, мы не потеряем записи на границах страницы (как всегда с оговоркой — при условии, что потом не появится новых с тем же временем).

      +1
      Очень интересная заметка. Выражаю благодарность. Как-то я прошляпил, что в 7 версии добавили миллисекунды. Еще в эру PHP 5.4+ у нас в проекте возникла потребность в миллисекундах, в итоге на свет родилось такое вот творение для работы с микросекундами (округлить вниз до миллисекунд — плевое дело) — github.com/Alroniks/dtms
      Библиотека, которая подменяет собой стандартные DateTime и DateInterval (нужно только use поправить по проекту). Сейчас вряд ли пригодна к использованию, так как не обновлял ее давно, но задумался, что в нее можно вдохнуть новую жизнь.
        +1

        Интересно. Кажется, что с помощью такой обёртки можно править баги при работе с временем, если обновить версию PHP нельзя или попался новый еще неисправленный баг.

        –1
        Не понял, как связаны временные метки с проблемными ситуациями. Везде сортировка и выборка должна осуществляться по ID записи, но никак не по времени. Это и дольше и по смыслу не особо нужно. Если нужна фильтрация во временном диапазоне у сущностей с фиксированными ID, то проблемная ситуация возможна, хотя опять же метки временные скорее всего не пригодятся.

        А есть реальные задачи, где время с такой точностью нужно?
          0

          У меня были различные логи которые писались в разные таблицы и имели разный форм, типа отправка почты, изменения статуса, отсправка запроса на сторонний сервис и так далее, и все это выполнялось в одном запросе, и чтобы потом адекватно понять последовательность действий добавил микрсекунды, после этого я смог адекватно вывести лог всех действий в админ-панели, в нужном порядке.

            0
            Так у них порядок свой внутренний в запросе был или они в случайном порядке это делали? Какой смысл их смешивать, если они действительно разные?
              0

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

            +1

            Сортировка по уникальному идентификатору — это очень хорошо и если можно её использовать, то лучше сделать это. К сожалению она не всегда подходит. Вот несколько примеров, где эта сортировка не поможет:


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

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

              +1
              Но в этих ситуациях повышенная точность времени не нужна, даже разбежки в несколько секунд простительны. Более того, мессенджер как-то свои сообщения локально хранит и их айдишники наверняка на время тоже завязаны.
                +1

                В своём первом комментарии вы говорите про две вещи:


                1. сортировки по времени не нужны, а нужны по ID
                2. непонятно, зачем нужна такая точность времени.

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


                Более того, мессенджер как-то свои сообщения локально хранит и их айдишники наверняка на время тоже завязаны.

                Я говорю не про сообщения, а про контакты (главную страницу любого мессенджера). Там записи обновляются много раз (при получении нового сообщения), а создаются (и получают идентификатор) только один раз.

                  –1
                  В отрыве от точности я могу и сам придумать десятки примеров, где что-то можно сортировать по времени. Тут же на время с точностью до дробных секунд опираются как на основную характеристику выборки, потому я её имею в виду как само обязательное условие.
              0
              А есть реальные задачи, где время с такой точностью нужно?

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


              Другой пример — замер времени доставки сообщений чата между базами данных отправителя и получателя. Это физически разные базы и хочется понимать, сколько времени происходит между отправкой сообщений (записью её в базу отправителя) и доставкой (базу получателя). При секундной точности разницу между 0.05 и 0.95 секунды будет не видна, а при повышении точности времени в базе — вполне.

                0
                Вот такие вещи уже интереснее. Но тут уже возникает вопрос о пользе такой точности исходя из этого. И в том и в другом случае важен именно порядок действий, а не время. За порядок всё же должен отвечать порядковый номер, некий «ID», а временное расстояние между элементами системы скорее всего нет нужды выяснять более чем один-два раза. Если операций выполняется много и в разных местах, логично замерять по ним какие-то обобщённые величины, время выполнения некого пакета действий, но не каждое с безумной точностью.
                  +2

                  Главная вещь, к которой я призываю — не использовать на автомате время с секундной точностью (потому что "её хватит всем"). Вместо этого стоит задумываться о том, не будет ли пользы от большей точности и если будет, то инструменты для работы с ней уже есть и их можно использовать. Если вам достаточно порядка, то в этом нет ничего плохого, не нужно использовать максимальную точность времени.

                +1
                Не понял, как связаны временные метки с проблемными ситуациями. Везде сортировка и выборка должна осуществляться по ID записи, но никак не по времени. Это и дольше и по смыслу не особо нужно.

                А есть реальные задачи, где время с такой точностью нужно?

                Жизненная ситуация — gps-терминалы для мониторинга передвижения. При отсутствии связи данные хранятся в памяти и передаются при восстановлении связи. Все бы хорошо, но многие модели передают накопленные данные в хаотичном порядке. А выводить надо по-порядку, по времени.

                Так же многие модели могут выдавать несколько пакетов в секунду, а значит и такая точность нужна.
                  –1
                  Так а ID своим записям они не проставляют? На время ориентироваться зачем?
                    0
                    Нет, там масса своих нюансов с сотнями разных терминалов от разных производителей.
                +5

                Немного пугает о том что в блоге Badoo пишут о Yii.

                  +2

                  Я хотел проиллюстрировать, как работают миграции с временем высокой точности. Изначально план был сделать несколько примеров для разных фреймворков, но в процессе я решил что достаточно будет и одного примера. В результате в статью попал тот, что был сделан первым (с Yii у меня лично самый большой практический опыт среди популярных фреймворков, поэтому начал именно с него).


                  Насколько мне известно, в кодовой базе Badoo нет Yii (но есть немного Laravel и Symfony Components).

                Only users with full accounts can post comments. Log in, please.