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 TIMEDATETIME, 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:

 

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.

Shuo Wang
Author: Shuo Wang

Leave a Reply

Your email address will not be published. Required fields are marked *