How to realize time carry in mysql?
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.
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.
These are the details of the explanation of time rounding in mysql. Please pay more attention to other related articles!
