Analysis on the Time Carry of MySQL

2021-12-13 17:26:42
OfStack

The accuracy of the default time types (datetime and timestamp) in MySQL is seconds. If the accuracy of the set time value is less than seconds, it will be rounded by 4 and 5, which may cause the value in the database to be 1 second longer than the original value. That is to say, the records that originally belonged to today may be recorded to tomorrow.

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 readers may not know that datetime and timestamp can be defined with precision, and the precision value is 0 ~ 6, which means that several decimal places are reserved, and the default value is 0. Obviously, keeping 3 bits can be regarded as millisecond accuracy, and keeping 6 bits can be regarded as microsecond accuracy.

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 it again select * from test_time You can see the following results by querying:

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 second values of time_sec and stamp_sec are carried in the database, and the millisecond values of time_millis and stamp_millis are carried.

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

Use datetime (6) or timestamp (6) when defining fields; Fields are defined without precision, but the millisecond value is truncated before the time is stored in the database.