An explanation of the time carry problem in mysql

After mysql was updated to 5.6.4, a new feature called functional seconds was added, which can record the millisecond value of time. However, the current database does not record millisecond values, so there will be a problem that the time in Java will be rounded if Milliseconds exceeds 500.

The following is an example that demonstrates how time is carried. First create a table:

CREATE TABLE test_time (
 time_sec   datetime,
 time_millis datetime(3),
 time_micros datetime(6),
 stamp_sec  timestamp,
 stamp_millis timestamp(3),
 stamp_micros timestamp(6)
);

Some friends may not know that datetime and timestamp can be defined with precision. The precision value is 0~6, which means how many decimal places are retained. The default value is 0. Obviously retaining 3 bits can be regarded as millisecond precision, and retaining 6 bits can be regarded as microsecond precision.

(Recommended online learning video tutorial: mysql video tutorial)

Then we insert a record:

INSERT INTO test_time
( time_sec, time_millis, time_micros,
 stamp_sec, stamp_millis, stamp_micros )
VALUES(
 '2019-11-30 12:34:56.987654',
 '2019-11-30 12:34:56.987654',
 '2019-11-30 12:34:56.987654',
 '2019-11-30 12:34:56.987654',
 '2019-11-30 12:34:56.987654',
 '2019-11-30 12:34:56.987654'
);

Then do the select again * From test_time query, you can see the following results:

time_sec             |time_millis            |time_micros               |stamp_sec            |stamp_millis           |stamp_micros              |
---------------------|-----------------------|--------------------------|---------------------|-----------------------|--------------------------|
2019-11-30 12:34:57.0|2019-11-30 12:34:56.988|2019-11-30 12:34:56.987654|2019-11-30 12:34:57.0|2019-11-30 12:34:56.988|2019-11-30 12:34:56.987654|

You can see that the seconds values ​​of time_sec and stamp_sec in the database have been rounded, and the millisecond values ​​of time_millis and stamp_millis have been rounded.

It can be seen that there are two methods to avoid such errors:

1. Use datetime(6) or timestamp(6) when defining fields;

2. There is no precision when defining the field, but the millisecond value must be truncated before storing the time in the database.

Recommended related articles and tutorials: mysql tutorial

The above is the detailed content of An explanation of the time carry problem in mysql. For more information, please follow other related articles on the PHP Chinese website!