High precision time: how to work with fractions of a second in MySQL and PHP

Once I caught myself thinking that when working with time in databases, I almost always use time accurate to the second simply because I am used to it and that this is the option described in the documentation and a huge number of examples. However, now this accuracy is far from enough for all tasks. Modern systems are complex - they can consist of many parts, have millions of users interacting with them - and in many cases it is more convenient to use greater accuracy, the support of which has existed for a long time.

In this article I will talk about ways to use time with fractional parts of a second in MySQL and PHP. It was conceived as a tutorial, so the material is designed for a wide range of readers and in some places repeats the documentation. The main value should be that I have collected in one text everything you need to know to work with such time in MySQL, PHP and the Yii framework, and also added descriptions of non-obvious problems that you may encounter.

I will use the term "high precision time". You will see the term “fractional seconds” in the MySQL documentation, but its literal translation sounds strange, but I have not found another established translation.

When to use high precision time?

For starters, I’ll show a screenshot of my inbox's inbox that illustrates the idea well:

Letters are the reaction of the same person to one event. A man accidentally pressed the wrong button, quickly realized this and corrected himself. As a result, we received two letters sent at about the same time, which are important to sort correctly. If the sending time is the same, there is a chance that the letters will be displayed in the wrong order and the recipient will be embarrassed, since then he will receive the wrong result for which he will count.

I came across the following situations in which time of high accuracy would be relevant:

1. You want to measure the time between some operations. Everything is very simple here: the higher the accuracy of timestamps at the boundaries of the interval, the higher the accuracy of the result. If you use whole seconds, then you can make a mistake for 1 second (if you fall on the borders of seconds). If you use six decimal places, then the error will be six orders of magnitude lower.
2. You have a collection where it is likely that there are several objects with the same creation time. An example is a chat familiar to everyone, where the contact list is sorted by the time of the last message. If there appears page-by-page navigation, then there is even a risk of losing contacts at the page borders. This problem can be solved without high accuracy time due to sorting and pagination by a pair of fields (time + unique identifier of an object), but this solution has its drawbacks (at least the complication of SQL queries, but not only that). Increasing the accuracy of time will help reduce the likelihood of problems and avoid complicating the system.
3. You need to keep a change history of some object. This is especially important in the service world, where modifications can occur in parallel and in completely different places. As an example, I can cite the work with photos of our users, where many different operations can be performed in parallel (the user can make the photo private or delete it, it can be moderated in one of several systems, cropped for use as a photo in chat, etc.). )

It must be borne in mind that one cannot trust the obtained values ​​by 100% and the real accuracy of the obtained values ​​can be less than six decimal places. This is due to the fact that we can get an inaccurate time value (especially when working in a distributed system consisting of many servers), the time can change unexpectedly (for example, when synchronizing via NTP or when changing the clock), etc. I will not dwell on all these problems, but I will give a couple of articles where you can read more about them:

• “The errors of programmers regarding time" (the article itself is very minimalistic, but in addition to the theses from the text, explanations can be found in the comments);
• "The heavy burden of time . "

Work with high precision time in MySQL

MySQL supports three types of columns in which time can be stored: TIME



, DATETIME



and TIMESTAMP



. Initially, they could only store values ​​that were multiples of one second (for example, 2019-08-14 19:20:21). In version 5.6.4, which was released in December 2011, it became possible to work with the fractional part of a second. To do this, when creating the column, you need to specify the number of decimal places, which must be stored in the fractional part of the timestamp. The maximum number of characters that is supported is six, which allows you to store time accurate to the microsecond. If you try to use more characters, you get an error.

Example:

 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)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

In this example, the timestamp of the inserted record has a zero fraction. This happened because the input value was indicated to the nearest second. To solve the problem, the accuracy of the input value must be the same as the value in the database. The advice seems obvious, but it is relevant, since a similar problem can emerge in real applications: we were faced with a situation where the input value had three decimal places, and six were stored in the database.

The easiest way to prevent the occurrence of this problem is to use the input values ​​with maximum accuracy (up to microseconds). In this case, when writing data to the table, the time will be rounded to the required accuracy. This is an absolutely normal situation that will not cause any warning (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; +---------+---------+-------------------------+ | chat_id | title | last_message_send_time | +---------+---------+-------------------------+ | 1 | Chat #1 | 2019-09-22 22:23:15.235 | +---------+---------+-------------------------+ 1 row in set (0.00 sec)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

When using automatic initialization and automatic updating of TIMESTAMP columns using a structure of the form DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP



it is important that the values ​​have the same precision as the column itself:

 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 functions for working over time support working with the fractional part of units of measure. I will not list them all (I suggest looking in the documentation), but I will give a few examples:

 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)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

The main problem associated with the use of the fractional part of seconds in SQL queries is the inconsistency of accuracy in comparisons ( >



, <



, BETWEEN



). You can encounter it if the data in the database has one accuracy, and in the queries - another. Here is a small example illustrating this problem:

 #         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   - ,     ,    
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

In this example, the accuracy of the values ​​in the query is higher than the accuracy of the values ​​in the database, and the problem occurs "on the border from above." In the opposite situation (if the input value has an accuracy lower than the value in the database) there will be no problem - MySQL will bring the value to the desired accuracy in both INSERT and 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)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

The consistency of the accuracy of values ​​should always be kept in mind when working with high precision time. If such boundary problems are critical for you, then you need to make sure that the code and the database work with the same number of decimal places.

See also:

• “Fractional Seconds in Time Values” ;
• “Automatic Initialization and Updating for TIMESTAMP and DATETIME” ;
• “Date and Time Functions” .

Work with high precision time in PHP

It is not enough to have high precision time in the database - you need to be able to work with it in the code of your programs. In this section I will talk about three main points:

1. Receiving and formatting time: I will explain how to get the time stamp before putting it into the database, getting it from there and doing some kind of manipulation.
2. Working with time in PDO: I'll show you an example of how PHP supports formatting time in a database library.
3. Working with time in frameworks: I’ll talk about using time in migrations to change the structure of the database.

Getting and formatting time

When working with time, there are several basic operations that you need to be able to do:

• getting the current point in time;
• getting a moment in time from some formatted string;
• adding a period to a point in time (or subtracting a period);
• getting a formatted string for a point in time.

In this part I will tell you what possibilities for performing these operations are in PHP.

The first way is to work with a timestamp as a number . In this case, in the PHP code we work with numerical variables, which we operate through functions such as time



, date



, strtotime



. This method cannot be used to work with high precision time, since in all these functions the timestamps are an integer (which means that the fractional part in them will be lost).

Here are the signatures of the main such functions from the official documentation:

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('Ymd H:i:s.u', $now); // 2019-09-11 21:27:18.000000 print date('Ymd H:i:s.v', $now); // 2019-09-11 21:27:18.000
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Also to this method include microtime



and hrtime



, which allow you to get a timestamp with a fractional part for the current moment. The problem is that there is no ready-made way to format such a label and get it from a string of a specific format. This can be solved by independently implementing these functions, but I will not consider such an option.

If you need to work only with timers, then the HRTime library is a good option, which I will not consider in more detail due to the limitations of its use. I can only say that it allows you to work with time to the nanosecond and guarantees the monotony of timers, which eliminates some of the problems that can be encountered when working with other libraries.

To fully work with fractional parts of a second, you need to use the DateTime module. With certain reservations, it allows you to perform all the operations listed above:

 //    : $time = new \DateTimeImmutable(); //      : $time = new \DateTimeImmutable('2019-09-12 21:32:43.908502'); $time = \DateTimeImmutable::createFromFormat('Ymd 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('Ymd H:i:s.v'); // '2019-09-12 21:32:43.908' print $time->format("Ymd H:i:su"); // '2019-09-12 21:32:43.908502'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 $time = \DateTimeImmutable::createFromFormat('Ymd H:i:s.u', '2019-09-12 21:32:43.9085'); // =>   DateTimeImmutable    2019-09-12 21:32:43.908500 $time = \DateTimeImmutable::createFromFormat('Ymd H:i:s.u', '2019-09-12 21:32:43.90'); // =>   DateTimeImmutable    2019-09-12 21:32:43.900000 $time = \DateTimeImmutable::createFromFormat('Ymd H:i:s.u', '2019-09-12 21:32:43'); // =>  false
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

The main problem of this module is the inconvenience when working with intervals containing fractional seconds (or even the impossibility of such work). The \DateInterval



although it contains the fractional part of a second accurate to the same six decimal places, you can only initialize this fractional part through DateTime::diff



. The constructor of the DateInterval class and the factory method \DateInterval::createFromDateString



can only work with whole seconds and do not allow you to specify the fractional part:

 //     -   $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"
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Another problem may arise when calculating the difference between two points in time using the \DateTimeImmutable::diff



method. In PHP prior to version 7.2.12, there was a bug due to which the fractional parts of a second existed separately from other digits and could receive their own sign:

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

In general, I advise you to be careful when working with intervals and carefully cover such code with tests.

See also:

• "Date and time" ;
• "Hrtime function."

Work with high precision time in PDO

PDO and mysqli are the two main interfaces for querying MySQL databases from PHP code. In the context of a conversation about time, they are similar to each other, so I will only talk about one of them - PDO.

When working with databases in PDO, time appears in two places:

• as a parameter passed to executed queries;
• as a value that comes in response to SELECT queries.

It’s good practice to pass placeholders when passing parameters to the request. Placeholders can transfer values ​​from a very small set of types: Boolean values, strings, and integers. There is no suitable type for date and time, so you must manually convert the value from an object of the DateTime / DateTimeImmutable class to a string.

 $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')]);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Using such a code is not very convenient, since each time you need to format the transmitted value. Therefore, in the Badoo code base, we implemented support for typed placeholders in our wrapper for working with the database. In the case of dates, this is very convenient, since it allows you to transfer a value in different formats (an object that implements DateTimeInterface, a formatted string or a number with a time stamp), and all the necessary transformations and checks of the correctness of the transferred values ​​are already done inside. As a bonus, when passing an incorrect value, we learn about the error immediately, and not after receiving an error from MySQL when executing the query.

Retrieving data from query results looks pretty simple. When performing this operation, PDO returns the data in the form of strings, and in the code we need to further process the results if we want to work with time objects (and here we need the functionality to get the time from the formatted string, which I talked about in the previous section).

 $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']); //  -  }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Note



The fact that PDO returns data as strings is not entirely true. When receiving values, it is possible to set the value type for the column using the PDOStatement::bindColumn



. I did not begin to talk about this because there is the same limited set of types that does not help with dates.

Unfortunately, there is a problem to be aware of. In PHP prior to version 7.3, there is a bug due to which PDO, when the PDO::ATTR_EMULATE_PREPARES



attribute is PDO::ATTR_EMULATE_PREPARES



"cuts off" the fractional part of a second when it is received from the database. Details and an example can be found in the bug description on php.net . In PHP 7.3, this error was fixed and warned that this change breaks backward compatibility .

If you are using PHP version 7.2 or older and are unable to update it or enable PDO::ATTR_EMULATE_PREPARES



, then you can work around this bug by correcting SQL queries that return time with a fraction so that this column has a string type. This can be done, for example, like this:

 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;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

This problem can also be encountered when working with the mysqli



module: if you use prepared queries by calling the mysqli::prepare



method, then in PHP prior to version 7.3, the fractional part of a second will not be returned. As with PDO, you can fix this by updating PHP, or bypassing the conversion of time to a string type.

See also:

• “Prepared queries and stored procedures in PDO” ;
• "Prepared queries in mysqli" ;
• Documentation for the mysqli_stmt :: bind_param method (here the types of placeholders supported in mysqli are described);
• Pool request to PHP, in which they fixed the "truncation" of the fractional part of seconds .

Work with high precision time in Yii 2

Most modern frameworks provide migration functionality that allows you to store the history of database schema changes in the code and incrementally change it. If you use migrations and want to use high precision time, then your framework should support it. Fortunately, this works out of the box in all major frameworks.

In this section I will show how this support is implemented in Yii (in the examples I used version 2.0.26). About Laravel, Symfony and others, I will not write in order not to make the article endless, but I will be glad if you add details in the comments or new articles on this topic.

In migration, we write code that describes changes to the data schema. When creating a new table, we describe all its columns using special methods from the \ yii \ db \ Migration class (they are declared in the SchemaBuilderTrait tray ). The time



, timestamp



and datetime



methods that can take an input value of accuracy are responsible for the description of columns containing date and time.

An example of a migration in which a new table is created with a high precision time column:

 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'); } }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

And this is an example of migration in which the accuracy in an existing column changes:

 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.

See also:

• Yii 2 ;
• SchemaBuilderTrait .

Conclusion

, , — , . , , . , !