How to use timestamp precision in mysql

This article will introduce to you the usage of 1901 timestamp precision in mysql. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to everyone.

Why does the DATETIME precision in mysql only support seconds?

Is the DATETIME type in mysql related to time zone?

When designing a table in mysql, how to choose the field that represents time?

Case Analysis DATETIME Accuracy Issue

Some time ago, the version of mysql-connector-java for the responsible application was upgraded from 5.1.16 to 5.1.30, and I discovered it when doing functional regression. , the runtime data of use cases similar to the above SQL will be missing, resulting in functional problems.

Considering that in the application I am responsible for, there is a function that requires the use of SQL similar to the following, that is, using the timestamp as the query condition to query all data after a certain timestamp.

After investigation, it was found that mysql-connector-java would discard the precision after seconds before 5.1.23 and then pass it to the MySQL server, which happened to be in the mysql version we used. The precision of DATETIME is seconds; after I upgraded mysql-connector-java to 5.1.30, when the timestamp is transmitted from the java application to the MySQL server through mysql-connector-java, the milliseconds will not be discarded. From the perspective of mysql-connector-java, it fixes a BUG, ​​but for my application, it triggers a BUG.

If you face this problem, how will you fix it?

We thought of three options at the time:

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

Before passing it to the Mapper interface, round up the incoming timestamp by seconds. The code is as follows

Before querying, decrement the incoming timestamp by 1 Seconds;

It has been verified that in Solution 1, the java.sql.Date object transferred from java.util.Date will lose all the precision after the date, resulting in more unnecessary data being queried; Solution 3 is possible, but one or two more data may be found; Option 2 is also possible, which is equivalent to compensating for the characteristics of mysql-connector-java in terms of code. In the end I chose option 2.

Case Reproduction

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

Use spirngboot mybatis as the development framework to define a user entity. The code is as follows:

Define the Mapper corresponding to the entity. The code is as follows:

Set the configuration related to connecting to mysql, the code is as follows:

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

When running a single test, as we imagined, no data was queried. The results are as follows:

Then modify the code and use the above code to correct the query timestamp by seconds. The code is as follows:

Run the single again Test, as we imagined, the data can be queried this time.

However, here is a small episode. When I first designed the table, the SQL statement I used was as follows,

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

Summary of knowledge points

After the previous actual case analysis and case recurrence, readers must have a certain understanding of the DATETIME type in mysql. Next, let’s take a look at what experiences we can summarize from this case.

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

The field types used to represent time in MySQL are: DATE, DATETIME, and TIMESTAMP. They have similarities and each has its own characteristics. I have summarized a table as follows:

The DATETIME type is stored as an integer in the format of "YYYYMMDDHHMMSS" in MySQL. It has nothing to do with the time zone and uses 8 bytes of space;

TIMESTAMP type can be saved The time range is much smaller, and the displayed value depends on the time zone. The MySQL server, operating system, and client connection all have time zone settings.

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

During development, you should try to avoid using timestamps as query conditions. If you must use them, you need to fully consider the accuracy of MySQL and the accuracy of query parameters.

Recommended learning: php video tutorial

The above is the detailed content of How to use timestamp precision in mysql. For more information, please follow other related articles on the PHP Chinese website!