Shulou(Shulou.com)05/31 Report--

How to use timestamp precision in mysql? In view of this problem, this article introduces the corresponding analysis and answers in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Some time ago, the version of mysql-connector-java for the responsible application was upgraded from 5.1.16 to 5.1.30. When doing functional regression, it was found that there were omissions in the runtime data of use cases similar to the above SQL, resulting in functional problems.

Considering that there is a feature in my application that needs to use a SQL like this, even if you use a timestamp as a condition for a query, query all data after a certain timestamp.

After investigation, it is found that mysql-connector-java will discard the precision after seconds before 5.1.23 and then transmit it to the MySQL server. It just so happens that the precision of DATETIME in the version of mysql we use is seconds. After I upgraded the mysql-connector-java to 5.1.30, when the timestamp was passed from the java application to the MySQL server through mysql-connector-java, the milliseconds were not discarded. From mysql-connector-java 's point of view, it fixed a BUG, but for my application, it triggered a BUG.

If you were faced with this problem, how would you fix it?

We thought of three options at that time:

Change the type of timestamp parameter in the Mapper interface of mybatis from java.util.Date to java.sql.Date

Before passing in the Mapper interface, correct the passed timestamp by second, as follows

Before querying, subtract the incoming timestamp by 1 second

After verification, scheme 1 will, the java.sql.Date object transferred by java.util.Date will lose all the precision after the date, resulting in querying more unnecessary data; scheme 3 is possible, that is, one or two more data may be found; scheme 2 is also possible, which is equivalent to compensating the characteristics of mysql-connector-java from the code. In the end, I chose option 2.

Case recurrence

Use homebrew to install MySQL, version 8.0.15. After installation, create a table to store user information. The SQL is as follows:

Using spirngboot + mybatis as the development framework, define a user entity, the code is as follows:

Define the Mapper corresponding to the entity, as shown below:

Set the configuration related to the connection mysql, as follows:

Write the test code, insert a piece of data first, and then use the timestamp as the query condition to query, the code is as follows:

Running a single test, as we imagine, does not query the data, and the results are as follows:

Then modify the code and use the above code to correct the timestamp of the query by seconds, as follows:

Run the single test again, as we envisioned, and this time we can query the data.

However, there is an interlude. When I first designed the table, I used the following SQL statement

Smart as you must have found, the datetime here already supports a smaller time precision after the decimal point, up to 6 digits, that is, up to a subtle level. When was this feature introduced? I looked up [MySQL's official documentation] [9] and found that it was supported after mysql 5.6.4.

Summary of knowledge points

After the previous actual case analysis and case reproduction, the reader must have a certain understanding of the type of DATETIME in mysql, and then follow me to see what experience we can sum up from this case.

The version of mysql-connector-java and the version of mysql need to be used together, for example, the version before 5.6.4, it is best not to use the version before 5.1.23 of mysql-connector-java, otherwise we may encounter the problem we encountered this time.

The types of fields used to represent time in MySQL are: DATE, DATETIME, and TIMESTAMP. There are similarities between them, and each has its own characteristics. I summarized a table as follows:

The DATETIME type is stored as an integer in "YYYYMMDDHHMMSS" format in MySQL, regardless of the time zone, using 8 bytes of space

The time range in which the TIMESTAMP type can be saved is much smaller, and the values displayed depend on the time zone. MySQL's server, operating system, and client connections all have time zone settings.

Generally speaking, it is recommended to use DATETIME as the timestamp field, and it is not recommended to use the bigint type to store time.

In development, the use of timestamps as query conditions should be avoided as far as possible. if necessary, the accuracy of MySQL and query parameters should be fully considered.

This is the answer to the question about how to use timestamp accuracy in mysql. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.