High-Precision Time: how to use fractional seconds in MySQL and PHP
Once I caught myself thinking about time in databases and realised that I nearly always used time with one-second precision just because it was a common way to it, as well as because that was exactly what was written in manuals in lots of examples. Today, however, this level of precisions is no longer sufficient for many applications. Modern systems are complex, they may consist of a large number of parts and have millions of users interacting with them, so in many cases, it would be often more useful to use more precise time values that have already been supported for a long time.
In this article, I describe how to use time with fractional seconds in MySQL and PHP. It was initially conceived as a tutorial, so its contents are intended for non-specialist audiences and sometimes repeat what is written in manuals. The main value of this article, I believe, is that I have put together everything you need to know to work with MySQL, PHP and Laravel framework, and have also included descriptions of some less obvious issues that may arise.
I’m going to use the term “high-precision time” in this article. In MySQL manuals, you will see the term “fractional seconds” used for the same thing.
When should high-precision time be used?
To start with, here is a screenshot of my inbox messages list which provides a good illustration:
Messages represent two responses of the same individual to the same event. The individual occasionally clicks a wrong button, realises the problem quickly and makes a correction. As a result, we have two messages sent at the same time which need to be sorted correctly. If the sending time is the same, there is a chance that messages will be displayed in the wrong order, which will confuse the recipient because the result will be different from what is expected.
I used to face the following situations where high-precision time would be useful:
- You want to measure the time interval between some actions. This scenario is simple: the higher the precision of timestamps at interval boundaries, the greater precision the result will have. If you use whole seconds (without fractional part), you can have 1 second error (at boundaries of seconds). However, if six decimals are used then the risk of error will be six orders of magnitude lower.
- You have a collection where there is a high probability of having several objects with the same creation time. As an example of this situation, consider a commonly used chat where the contacts list is sorted by the time of the most recent message. If page-by-page navigation appears there, then there will even be the risk of losing objects at page boundaries. This issue can be also resolved without high-precision time through sorting and page-by-page breaking by pairs of margins (time + unique object identifier), but this solution has its downside (more complex SQL queries as a minimum, and other issues beyond that). Higher time precision will help reduce the probability of issues arising and avoid system complications.
- You need to store a history of changes for some object. This is particularly important in the world of microservices, where modifications may occur in parallel and in different places. As an example, consider the way we work with photos of our users, where a number of different actions may be performed in parallel (a user can make a proto private or remove it, the photo can be moderated in one or several systems, cropped for use as an image in chat, etc.).
It should be kept in mind that the values obtained can never be 100% reliable, and the actual precision of values obtained can be lower by up to six decimals. This happens because we can have a non-precision time value (especially while working in a distributed system comprising multiple servers), and time can change unexpectedly (for instance, in case of NTP synchronisation or time change), etc. I’m not going to be too specific about those issues here, I would rather simply provide a couple of links for more details:
- Falsehoods programmers believe about time
- More falsehoods programmers believe about time; “wisdom of the crowd” edition
Using high-precision time in MySQL
MySQL supports three types of columns where time can be stored: TIME, DATETIME and TIMESTAMP. Originally, only multiples of one second could be stored there (e.g. 2019–08–14 19:20:21). Fractional seconds have been supported since version 5.6.4, which was released in December 2011. To use it, specify the number of decimals to be stored in the column definition. The maximum supported number is six, which allows storing time with one-microsecond precision. If you try to use more decimals, an error will be returned.
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: 0Test> 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 record pasted in here has no fractional seconds part. This is because the input value has been specified with one-second precision. To resolve the issue, the input value needs to have the same precision as the value stored in the database. This tip seems to be obvious, but it is still useful since a similar issue can arise in actual applications: we had a situation where the input value had three decimals while the database value had six.
The easiest way to prevent this issue from arising is to use input values with maximum precision (a microsecond). In this case, time will be rounded to the required precision when data is recorded in the table. This is an absolutely normal situation, and it will not trigger any warnings:
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: 0Test> 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 initialisation and automatic updates of TIMESTAMP type columns with a structure like DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, it is important to make sure that the values have the same precision that the column has:
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: 0Test> UPDATE ChatContactsList SET last_message_send_time='2020-04-10 22:22:22' WHERE chat_id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0Test> SELECT * FROM ChatContactsList\G
*************************** 1. row ***************************
chat_id: 1
title: Chat #1
last_message_send_time: 2020-04-10 22:22:22.000
updated: 2020-04-16 13:17:15.039
1 row in set (0.00 sec)
The main issue associated with the usage of fractional seconds in SQL queries is the precision mismatch in comparisons (>, <, BETWEEN). This may arise if the precision of data in the database differs from precision in the queries to this data. Here is a small example illustrating the issue:
# Six decimals in fractional part are at input
Test> INSERT INTO ChatContactsList (title, last_message_send_time) VALUES ('Chat #2', '2020-04-10 21:16:39.123456');
Query OK, 1 row affected (0.00 sec)Test> SELECT chat_id, title, last_message_send_time FROM ChatContactsList WHERE title='Chat #2'\G
*************************** 1. row ***************************
chat_id: 2
title: Chat #2
last_message_send_time: 2020-04-10 21:16:39.123
1 row in set (0.00 sec)
# ^^^ last_message_send_time keeps three decimals due to precision specified in column# last_message_send_time condition uses the value from the INSERT
Test> SELECT title, last_message_send_time FROM ChatContactsList WHERE last_message_send_time >= '2020-04-10 21:16:39.123456';
+---------+-------------------------+
| title | last_message_send_time |
+---------+-------------------------+
| Chat #1 | 2020-04-10 22:22:22.000 |
+---------+-------------------------+
1 row in set (0.00 sec)
# ^^^ Chat #2 not found because precision in database is lower than at input
In this example, the precision of values in the query is higher than the precision of values in the database, and the issue arises at the “boundary on top”. In the reverse situation (if value precision at the input is lower than in database), there won’t be any problem as MySQL will bring the value to the desired precision in both INSERT and SELECT:
Test> INSERT INTO ChatContactsList (title, last_message_send_time) VALUES ('Chat #3', '2020-04-01 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 <= '2020-04-01 21:20:19.1';
+---------+-------------------------+
| title | last_message_send_time |
+---------+-------------------------+
| Chat #3 | 2020-04-01 21:20:19.100 |
+---------+-------------------------+
1 row in set (0.00 sec)
Mismatch between values should always be kept in mind when using high-precision time. If such boundary issues are critical to you, then you need to make sure that the number of decimals used in code and in the database is always equal.
Side note: thoughts about choosing the precision of fractional seconds columns
Space occupied by fractional part of a time unit depends on the number of decimals in the column. It appears natural to select customary values: three or six decimals. But it’s not so simple in the case of three decimals. In fact, MySQL uses one byte to store two fractional part decimals:
It turns out that if you choose three decimals you will not use the occupied space to the full extent, and you could well take four decimals with the same storage overhead. I would generally recommend that an even number of decimals should always be used, with unnecessary decimals being “cut off” at output if necessary. But the ideal option is not to be too greedy and take six decimals. In the worst case scenario (DATETIME type), this column would occupy 8 bytes, that is, as much as a whole number would do in BIGINT type column.
See also:
- “Fractional Seconds in Time Values”;
- “Automatic Initialization and Updating for TIMESTAMP and DATETIME”;
- “Date and Time Functions”.
Using high-precision time in PHP
Just having high-precision time in your database is not enough — you need to be able to use it in your code as well. I’m going to cover three key points in this section:
- Obtaining and formatting time: I’m going to explain how to obtain a timestamp before putting it in database, how to retrieve it from database, and how to manipulate it.
- Using time in PDO: I’m going to show how PHP supports time formatting in database library using examples.
- Using time in Laravel framework: I’m going to tell how to use time in migrations, query builders and models in one of the modern frameworks.
Obtaining and formatting time data
There are several basic actions one needs to be able to perform when using time values:
- Obtaining current time
- Obtaining a time value from a formatted string
- Adding/subtracting a time interval to/from a time value
- Obtaining a formatted string for a time value.
In this part, I’m going to talk about what capabilities PHP offers for performing those actions.
The first way is to use timestamp as a number. In this case, we are dealing with numerical variables in PHP code, which we operate through such functions as time, date, strtotime. This way cannot be used for high-precision time handling because in all those functions timestamps are whole numbers (so the fractional part will be lost).
Here are signatures of the main functions of this type from official manuals:
time ( void ) : int
https://www.php.net/manual/en/function.time.phpstrtotime ( string $time [, int $now = time() ] ) : int
http://php.net/manual/en/function.strtotime.phpdate ( string $format [, int $timestamp = time() ] ) : string
https://php.net/manual/en/function.date.phpstrftime ( string $format [, int $timestamp = time() ] ) : string
https://www.php.net/manual/en/function.strftime.php
Side note: formatting using date function
Though fractional seconds cannot be transferred to those functions at input values, it is still possible to specify millisecond and microsecond characters in formatting string of date function. This way, you will get the relevant number of zeros in the function result.
Example:
$now = time();
print date('Y-m-d H:i:s.u', $now);
// 2019–12–11 21:27:18.000000
print date('Y-m-d H:i:s.v', $now);
// 2019–12–11 21:27:18.000The functions microtime and hrtime, which make it possible to obtain timestamp with a fractional part for the current time, can also be referred to the first way. The issue is that there is no readily available way for formatting such stamp and obtaining it from a string of a particular format. One could resolve those issues by implementing those functions oneself, but I’m not going to consider such an option here.
If you need to work only with timers, HRTime library might be a good option, but I’m not going to discuss it here due to its limited application. I would just say, though, that this library makes it possible to use time values with one-nanosecond precision and makes timers monotonic, thus eliminating some of the issues one may face when working with other libraries.
DateTime module can be used to handle fractional seconds properly. Usage example:
// Obtaining current time:
$time = new \DateTimeImmutable();
// Obtaining a time value from a formatted string:
$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');
// Adding/subtracting an interval:
$period = \DateInterval::createFromDateString('5 seconds');
$timeBefore = $time->add($period);
$timeAfter = $time->sub($period);
// Obtaining a formatted string for a time value:
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'Side note: less obvious aspect of using DateTimeImmutable::createFromFormat
The letter u in formatting string means microseconds, but it also works correctly in the case of fractional seconds with lesser precision. Moreover, this is the only way to specify fractional seconds in format string. Example:
$time = \DateTimeImmutable::createFromFormat('Y-m-d H:i:s.u', '2019–09–12 21:32:43.9085');
// => $time is a DateTimeImmutable object with timestamp 2019–09–12 21:32:43.908500 inside$time = \DateTimeImmutable::createFromFormat('Y-m-d H:i:s.u', '2019–09–12 21:32:43.90');
// => $time is DateTimeImmutable object with timestamp 2019–09–12 21:32:43.900000 inside$time = \DateTimeImmutable::createFromFormat('Y-m-d H:i:s.u', '2019–09–12 21:32:43');
// => $time is false
The main problem with this module is the inconvenience (or even impossibility) of using intervals that contain fractional seconds. Though \DateInterval class contains a fractional part of a second with the same six-decimal precision, this fractional part can only be initialised through DateTime::diff. DateInterval class constructor and \DateInterval::createFromDateString factory method are able to handle only whole seconds and do not allow for a fractional part to be specified:
// This string will return an exception due to incorrect format
$buggyPeriod1 = new \DateInterval('PT7.500S');
// This string will process and return interval object, but without seconds
$buggyPeriod2 = \DateInterval::createFromDateString('2 minutes 7.5 seconds');
print $buggyPeriod2->format('%R%H:%I:%S.%F') . PHP_EOL;
// Will return "+00:02:00.000000"Another issue may arise when the difference between two time values is calculated using \DateTimeImmutable::diff method.In pre-7.2.12 PHP versions there was a bug due to which fractional parts of a second existed separately from other digits and could receive their own signs:
$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;
// In a 7.2.12+ PHP version, the result will be "+1 days 14:52:54.135802"
// In an earlier version, we will have "+1 days 14:52:55.-864198"I would generally advise handling intervals with caution and carefully covering such code with tests.
See also:
Using high-precision time in PDO
PDO and mysqli are two main interfaces for making queries to MySQL databases from PHP code. In the context of the topic of time, they are similar to each other, so I’m going to talk about one of them, PDO.
During operations with a database in PDO, time is used in two instances:
- as a parameter being transferred to queries being performed;
- as a value being received in response to SELECT queries.
It is a good practice to use placeholders when transferring parameters to a query. Values from a very limited set of types can be transferred to placeholders: boolean values, strings and whole numbers. There is no suitable type for date and time, so a value needs to be converted manually from a DateTime/DateTimeImmutable class object 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('Y-m-d H:i:s.u')]);It’s not very convenient to use such code because you have to copy & paste the formatting code every time. To solve this problem in our codebase, we have implemented the support of typed placeholders in our database wrapper classes. In the case of dates, it’s very convenient because a value can be transferred in different formats (object implementing DateTimeInterface, formatted string or a number with timestamp), and necessary conversions and validity checks of transferred values are performed within it. As a bonus, when an invalid value occurs, we are informed about the error even before the query is sent to MySQL server.
Usage of the data from SQL query result looks quite simple. When this action is performed, PDO returns data as string values, and we have to additionally process the results in code if we are to use time objects (and here we will need a functionality for time value retrieval from a formatted string which was discussed in the preceding section).
Doing something useful
$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']);
// Doing something useful
}Side note:
It’s not quite true that PDO always returns query results as string values. It’s possible to specify column value types using the PDOStatement::bindColumn method. I didn’t mention this because the same limited set of types exists there which would not help in the case of dates.
Unfortunately, there is an issue here you need to be aware of. In pre-7.3 PHP versions, there is a bug which causes PDO with attribute PDO::ATTR_EMULATE_PREPARES disabled to “cut off” the fractional part of a second at receipt from database. See the description of the bug at php.net for more details and practical example. The bug was fixed in PHP 7.3 with a warning that the fix breaks backward compatibility.
If you use PHP Version 7.2 or older, and you are unable to update it or enable PDO::ATTR_EMULATE_PREPARES, you still can avoid this bug by casting datetime values to strings in your SQL queries. This can be done as in the example below:
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 issue may also arise when mysqli module is used: if you are using prepared statements by way of mysqli::prepare method invocation, then the fractional part of a second will not be returned in pre-7.3 PHP versions. As for PDO, this bug can either be fixed by updating PHP or circumvented by conversion of time to string type.
See also:
- Prepared statements and stored procedures in PDO;
- Prepared statements in mysqli;
- Manuals for method mysqli_stmt::bind_param (types of placeholders supported in mysqli are described here);
- Pull request to PHP in which fractional part of a second “cut-off” bug was fixed.
Using high-precision time in Laravel
I have found three main places where you can use date-time values in Laravel framework: database migrations, query builders and Eloquent models. I will cover all of them here. I have used Laravel v6.6.2 for the examples.
Migrations are the way to describe database schema changes in your code. Laravel migration is a class which contains two methods: up and down. Up is used to apply changes, down is used to revert.
In migration code, you can describe columns using these time-related type methods of Blueprint class: timestamps, timestamp, timestampTz, datetime, datetimeTz, time, timeTz. In the case of MySQL Tz-methods are useless because they behave the same way as non-Tz.
Datetime precision for these methods was added in Laravel 5.5 (see release notes — https://github.com/laravel/framework/releases/tag/v5.5.0) which was released in August 2017.
An example of adding new timestamps columns with fractional seconds:
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;class CreateNewsTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('news', function (Blueprint $table) {
$timestampsPrecision = 3;$table->bigIncrements('id');
$table->string('title', 128);
$table->longText('content');
$table->timestamps($timestampsPrecision);
$table->dateTime('published_at', $timestampsPrecision)->nullable(true);
});
}/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('news');
}
}
In this migration, I’ve created a new table with two timestamp columns (created_at & updated_at) and a datetime column (published_at). All these columns have 3 digits after the decimal point.
See also:
Database query builder provides an interface for creating and running database queries. In the context of date-times, we are interested in two things: setting parameters for queries and using the values from results. Input parameters are cast to strings and you can use strings with manual formatting or objects that implement DateTimeInterface. Here’s an example of date-time usage as an input param:
$value = '2020-01-18 12:56:45.921001';
$result = DB::table('news')
->where('created_at', '>=', $value)
->get();There two things to remember here:
- you should not use more fractional digits that you have at your database.
- you cannot use numeric timestamps as an input param because it won’t be cast to a properly formatted date-time string
The date-time values at response are represented as strings, so I have nothing more to say about them.
See also:
Eloquent ORM is an ActiveRecord implementation for working with your database. Each table is represented as a class extended from Eloquent Model and each row from this table is an object of this class and property values in these objects are values of rows’ columns. Eloquent models use Carbon library to represent date-time values.
Side note: Carbon objects
Carbon is a small library. It consists of several classes, but we are interested in two of them \Carbon\Carbon and \Carbon\Immutable which are extended from \DateTime and \DateTimeImmutable. Thus these classes implement DateTimeInterface and can be used as regular date-time objects.
By default, only created_at and updated_at are understood as date-time values and replaced as Carbon objects, but you can set this behaviour for other attributes as well. All you need to do is to add the names of these attributes into $dates property of your model. An example:
<?php
namespace App;use Illuminate\Database\Eloquent\Model;class News extends Model
{
public $timestamps = true; public $dates = [
'published_at',
];
}
Having done that, you will get a Carbon object as published_at property value (instead of string) and, you can use various types of value to set such attributes:
- int (timestamp)
- datetime string
- objects which implement DateTimeInterface (e.g. Carbon objects)
There are several caveats here.
First of all, by default Laravel uses date format without fractional part of seconds. You can change this by setting the custom value of $dateFormat property, which is used to cast string values to Carbon objects:
class News extends Model
{
protected $dateFormat = 'Y-m-d H:i:s.u';
...The second thing is that all string values of date-time columns should have the format described in dateFormat. If your value doesn’t match you will give an exception. It can be avoided for explicit property setting via using DateTime or Carbon objects, but it’s used in the core of model class on data loading from the table. So, if your date-time column values don’t match to this format you cannot read the values of matching attribute in your Eloquent model. If you need to solve this problem you need to override asDateTime method in your model class.
More info:
Conclusion
I hope I’ve succeeded in demonstrating here that high-precision time is a useful thing which can be used even today. If you become more conscious of the use of time precision in your projects after reading this article, then I will consider my job done. Thank you for reading to the end!
