Shulou(Shulou.com)06/01 Report--

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

Here is an example of how time carries. 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. A precision value of 0 to 6 means that a few decimal places are retained and the default value is 0. Obviously, the retention of 3 bits can be regarded as milliseconds, and the retention of 6 bits can be regarded as microseconds.

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-time_micros 34-time_micros, stamp_sec, stamp_millis, stamp_micros) VALUES (' 2019-11-30 12-12 time_micros 34-340-56.98-7654, '2019-11-30 12-14 14-34-14,' 2019-11-30 12-14-34-14, '2019-11-30 12-34-14,' 2019-11-30 12-34-34, 2019-11-30 12-34, 56.98-7654, 2019-11-30, 2019-11-30, 2019-11-30, 2019-11-30)

Then do another select * from test_time query to see the following result:

Time_sec | time_millis | time_micros | stamp_sec | stamp_millis | stamp_micros |

-|-|-- |-|- -|

2019-11-30 12MAV 34MAV 57.0 | 2019-11-30 12MAV 34MAX 56.988 | 2019-11-30 12MAV 34MAV 56.987654 | 2019-11-30 12MAV 34MAV 57.0 | 2019-11-30 12MAV 34MAV 56.988 | 2019-11-30 1234MU 56.987654 |

You can see that the second values of time_sec and stamp_sec are rounded in the database, and the millisecond values of time_millis and stamp_millis are rounded.

From this we can see that there are two ways to avoid such errors:

Use datetime (6) or timestamp (6) when defining fields; define fields with no precision, but truncate the millisecond value before storing the time in the database.

Related documentation:

MySQL 5.6 Reference: Fractional Seconds in Time Values

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.