A Brief Analysis on the Time Carrying Problem of MySQL

The default time type (datetime and timestamp) in MySQL has a precision of seconds. If the time value is set with a precision less than a second, it will be rounded off, which may cause the value in the database to be one second more than the original value. In other words, records that originally belonged to today may be recorded tomorrow.

Below is an example that demonstrates how time is rounded. 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. The precision value is 0~6, indicating how many decimal places are retained. The default value is 0. Obviously, retaining 3 digits can be regarded as milliseconds precision, and retaining 6 digits can be regarded as microseconds precision.

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 another select * from test_time query and you will 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 second values ​​of time_sec and stamp_sec in the database are rounded up, and the millisecond values ​​of time_millis and stamp_millis are rounded up.

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

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

Related Documents:

MySQL 5.6 Reference: Fractional Seconds in Time Values

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.