Solve the problem of inconsistent MySQL storage time

After obtaining the system time using Java and storing it in the MySQL database, when the time type is datetime or Timestamp, it is found that the database storage is inconsistent with the local time.

A big reason is that the time zone set for MySQL is inconsistent with your local time zone. The solution is to modify the MySQL time zone configuration, which you can find by searching online.

I recommend the second method

Just set it in the DBUtil class that connects to the database according to the actual needs of your project. For example, I changed the time zone to the same time zone as Shanghai, Asia in the following code.

private static String driver = "com.mysql.cj.jdbc.Driver";
    private static String url = "jdbc:mysql://127.0.0.1:3306/mypetstore?serverTimezone=Asia/Shanghai&useSSL=false";

serverTimezone=Asia/Shanghai is used to set the time zone.

When retrieving Timestamp data from MySQL and displaying it in the browser, there is always a .0 at the end, which is very annoying. What can I do?

You can use the following EL expression and JSTL tags to format it, where log.date is the time data found

<fmt:formatDate value="${log.date}" type="date" pattern="yyyy-MM-dd HH:mm:ss" />

Note: If you write it as yyyy-MM-dd hh:mm:ss, it will not distinguish between morning and afternoon

Supplement: Mysql storage time or date misalignment problem

Here is a discussion about the issue of the date being misplaced by one day or the time being incorrect when adding records to the MySQL database.

Problem scenarios

There is no problem with the code logic. When executing it step by step, I found that the date was correct when it was saved, but it was wrong when it was saved to the database.

reason

The reason is the problem with the parameters you added when configuring the data source connection. If I guess correctly, the serverTimezone parameter you configured should be UTC. This is because the wrong time zone is used, which causes the date to be misplaced when storing data.

Solution

Set serverTimezone to Asia/Shanghai, as shown below. Try again and the problem will be solved.

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.