Writing in the front

Recently, I encountered two cases of mysql timestamp related problems. One was that the accuracy of mysql-connector-Java and MSYQL was inconsistent, so the data could not be checked. Another example is that the data cannot be queried due to an incorrect time zone on the application server. In this article, we hope to answer some questions about time stamps in mysql:

  1. Why is DATETIME accuracy only supported up to seconds in mysql?
  2. Does the DATETIME type in mysql depend on time zone?
  3. Mysql > select * from ‘time’;

Case analysis of the accuracy of DATETIME

Recently, I upgraded the version of mysql-connection-Java of the application I was responsible for from 5.1.16 to 5.1.30. During the function regression, I found that the run-time data of the use cases that used SQL similar to the above would be omitted, resulting in functional problems.

Consider that there is a function in the application THAT I am responsible for that requires SQL like the following, that is, the time stamp is used as the condition of the query to query all data after a certain time stamp.

Before 5.1.23, mysql-connector-Java will discard the accuracy of DATETIME after second and then pass it to mysql server. When I update mysql-connector-Java to 5.1.30, I don’t discard milliseconds when I send time stamps from Java applications to mysql servers via mysql-connector-Java. This fixes a BUG from the mysql-connector-Java perspective, but for my application it triggered a BUG.

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

We came up with three options:

  • Date: java.util.Date: java.sql.date: java.sql.date: java.util.Date: java.sql.date: java.sql.date: java.util.Date: java.sql.date;

  • Before passing the Mapper interface, the incoming timestamp is positive by second, as shown below

  • Before querying, subtract 1 second from the passed timestamp;

The java.sql.Date object will lose all the accuracy after the Date, resulting in more unnecessary data query. Plan 3 is ok, but one or two more data may be detected. Option 2 is also acceptable, which compensates for the mysql-connector-Java features in code. Finally, I chose plan 2.

Case retrieval

MySQL > install MySQL from homebrew (version 8.0.15);

Using Spirngboot + Mybatis as the development framework, define a user entity as follows:

Define the Mapper for this entity as follows:

Mysql > connect to mysql

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

Running the single test, as we expected, did not query the data, the result is as follows:

Then modify the code, using the above code to query the time stamp positive by second, code as follows:

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

But here’s the catch: when I originally designed my table, I used an SQL statement like this,

As you may have noticed, datetime already supports up to six decimal places of accuracy, up to subtlety. When this feature was introduced, I checked [MySQL official documentation][9] and found that this feature was supported after MySQL 5.6.4.

Knowledge summary

After the previous actual case analysis and case replay, readers must have a certain understanding of the type of mysql DATETIME. Next, follow me to see what lessons we can draw from this case.

  1. The mysql-connector-Java version and mysql version need to be used together. For example, before 5.6.4, it is better not to use mysql-connector-Java version after 5.1.23, otherwise you may encounter the problem we encountered this time.
  2. The types of fields used to represent time in MySQL include DATE, DATETIME and TIMESTAMP. There are similarities among them and each has its own characteristics. I summarized a table as follows:
  3. DATETIME in MySQL is stored as an integer in the YYYYMMDDHHMMSS format. It is independent of the time zone and uses 8 bytes of space.
  4. The TIMESTAMP type can hold a much smaller time range, and the value displayed depends on the time zone. The MySQL server, operating system, and client connection are all set to the time zone.
  5. In general, it is recommended to use DATETIME as the timestamp field. Bigint is not recommended to store time.
  6. In the development, should try to avoid the use of timestamp as a query condition, if must use, then need to fully consider the accuracy of MySQL and the precision of query parameters and other issues.

The resources

  1. Dev.mysql.com/doc/refman/…
  2. High Performance MySQL

This issue focuses on topics such as backend technology, JVM troubleshooting and optimization, Java interview questions, personal growth and self-management, providing readers with front-line developer work and growth experience, looking forward to your harvest here.