MySQL — The DATETIME column rounding causes data inconsistency

Shuo Wang

6 years ago

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:

 CREATE TABLE fractest( c1 DATETIME(3) );

1	CREATE TABLE fractest( c1 DATETIME(3) );

which allows 3 fractional digits.