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 accuracy problem.
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.
So my MySQL server version is support microseconds precision.
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)
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:
CREATE TABLE fractest( c1 DATETIME(3) );
which allows 3 fractional digits.