MySQL — The DATETIME column rounding causes data inconsistency

Description:

Recently, when i checked my data on two MySQL servers( version: 5.7.17 ), i found data differences between to servers, which surprised me. Because i inserted some datetime values into them( or i think ), then i supposed maybe it’s the rounding on a DATETIME column.

First of all, check the audit log, it’s actually different of these two INSERTs, ‘2018-07-27 10:59:59.000’ and ‘2018-07-27 10:59:59.881’, and the column definition is not allowed the fractional seconds. So it was rounding caused the difference.

Read the Reference Manual( https://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html ) :

MySQL 5.6.4 and up expands fractional seconds support for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision:

So my MySQL server version is support microseconds precision.

Inserting a TIME,DATE, or TIMESTAMP value with a fractional seconds part into a column of the same type but having fewer fractional digits results in rounding, as shown in this example:

mysql> CREATE TABLE fractest( c1 TIME(2), c2 DATETIME(2), c3 TIMESTAMP(2) ); Query OK, 0 rows affected (0.33 sec) mysql> INSERT INTO fractest VALUES     > ('17:51:04.777', '2014-09-08 17:51:04.777', '2014-09-08 17:51:04.777'); Query OK, 1 row affected (0.03 sec) mysql> SELECT * FROM fractest; +-------------+------------------------+------------------------+ | c1          | c2                    | c3                    | +-------------+------------------------+------------------------+ | 17:51:04.78 | 2014-09-08 17:51:04.78 | 2014-09-08 17:51:04.78 | +-------------+------------------------+------------------------+ 1 row in set (0.00 sec)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

mysql> CREATE TABLE fractest( c1 TIME(2), c2 DATETIME(2), c3 TIMESTAMP(2) );   Query OK, 0 rows affected (0.33 sec)   mysql> INSERT INTO fractest VALUES       > ('17:51:04.777', '2014-09-08 17:51:04.777', '2014-09-08 17:51:04.777');   Query OK, 1 row affected (0.03 sec)   mysql> SELECT * FROM fractest;   +-------------+------------------------+------------------------+   | c1          | c2                    | c3                    |   +-------------+------------------------+------------------------+   | 17:51:04.78 | 2014-09-08 17:51:04.78 | 2014-09-08 17:51:04.78 |   +-------------+------------------------+------------------------+   1 row in set (0.00 sec)

 

No warning or error is given when such rounding occurs. This behavior follows the SQL standard, and is not affected by the server’s sql_mode setting.

So, everything is illuminated. I inserted a two different values( just millisecond value is not the same, and this value is not useless for me, i just need the DATETIME value is accurate to the second. ). But the ROUNDING to the second made the value changed.

How to fix:

Try to cut off the millisecond value while inserting.

Allowing the millisecond value storing,

For example:

which allows 3 fractional digits.