Today to talk about the topic of a simple, this is a friend asked me on WeChat, for starters I very can understand the worries of this kind of problem, various attempts, all kinds of search, others say things, but is not solve their own problems, today I simply from two aspects, and we talk about this problem, if you have any other friends in the solution, You can also leave a message to share.

This problem can be analyzed from two aspects:

  1. MySQL itself.
  2. Java code issues.

1. MySQL is faulty

SQL > select * from ‘MySQL’ where ‘time’ = ‘time’;

select now();
Copy the code

As you can see, the time of MySQL is 8 hours different from that of my system. The time of MySQL itself is not correct, so your future insert/query time is definitely not correct.

Note that you can use either the command line or a database tool like Sqlyog, Navicat, or Sequel Pro to perform this query. Do not use JDBC for this reason, as you will see in the second section.

MySQL > set time zone for MySQL

MySQL > select * from time zone;

show variables like '%time_zone%';
Copy the code

MySQL says its time zone is SYSTEM. What is SYSTEM? The first one says SYSTEM is UTC. Our Beijing time is 8 hours ahead of UTC, or UTC+8.

So now we want to change the MySQL time zone to right, can be achieved by modifying the configuration file (/ etc/MySQL/MySQL. Conf. D/mysqld. CNF), as follows:

MySQL > change time zone;

As you can see, the MySQL time zone is now normal.

Select now(); There would be no problem:

You can also use SQL to change the time zone:

set global time_zone = Asia/Shanghai
Copy the code

Note that we are in Asia/Shanghai time zone, friends do not write freely about other cities.

First we need to make sure MySQL is ok.

2. The JDBC connection is faulty

If your MySQL time is still incorrect after confirming that there is no problem with MySQL, you may have a JDBC connection problem.

Here I use the common JdbcTemplate as an example, other database framework operations are the same, I will mainly demonstrate the time zone problem, data manipulation details will not be shown.

First, let’s prepare a table, as follows:

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `createTime` datetime DEFAULT NULL,
  `updateTime` timestamp NULL DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Copy the code

CreateTime = dateTime; updateTime = Timestamp;

Then add a record to the table:

And the time zone of this database is Asia/Shanghai

Next we create a Spring Boot project that introduces Web, JDBC API dependencies, and MySQL drivers as follows:

MySQL > select * from ‘MySQL’;

spring.datasource.username=root
spring.datasource.password=123
spring.datasource.url=jdbc:mysql:///test01? serverTimezone=UTC
Copy the code

In the database connection address, I set the time zone to UTC. This time zone is 8 hours slower than our current time zone. Let’s see what happens if we use the wrong time zone.

@Autowired
JdbcTemplate jdbcTemplate;
@Test
void contextLoads(a) {
    List<User> list = jdbcTemplate.query("select * from user".new BeanPropertyRowMapper<>(User.class));
    System.out.println("list = " + list);
}
Copy the code

As you can see, this query results at 21:00, which is 8 hours faster than 13:00.

Why?

Because serverTimezone=UTC parameter is added to our connection address, the system will regard the data queried from the database as UTC time, that is, 13 o ‘clock as UTC time, but my current device is Asia/Shanghai time zone. When the UTC time zone changes from 13 o ‘clock to Asia/Shanghai, it will be 21 o ‘clock.

ServerTimezone =Asia/Tokyo, set the time zone to Tokyo, Tokyo is 1 hour earlier than us, Tokyo 13:00 is our 12:00, so the final query result will be 12:00.

As you can see from this example, the time zone of the JDBC connection parameter takes precedence over the time zone of the MySQL server, so you should pay special attention to this connection parameter as well.

3. The digression

Some people encounter a different kind of time zone problem, return JSON time error.

If Jackson is used in a project and the @jsonFormat annotation is used to format the date, time zone issues can occur as follows:

@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "Asia/Shanghai")
Copy the code

As you can see, if the timezone property is not set in this code, the default timezone is UTC, which will result in the final time difference of 8 hours.

4. Summary

Ok, this is the database summed up by Songko several situations, partners if there is any supplementary welcome to leave a message to discuss.