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

This article will explain in detail how to analyze the time carry problem in mysql. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

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

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 partners 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.

(online learning video tutorial recommended: 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-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 12 purge 34buret 57.0 | 2019-11-30 12 purge 34purl 56.988 | 2019-11-30 12 purge 34drex 56.987654 | 2019-11-30 12:34: 57.0 | 2019-11-30 12-340-340-56.988 | 2019-11-30-1212-340-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:

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

2. Define the field without precision, but truncate the millisecond value before saving the time in the database.

On how to analyze the mysql time carry problem to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.