Detailed explanation of how to avoid the trap of url time zone in mysql

Recently when using jars of mysql 6.0.x or above, you need to specify serverTimezone in the link of the code url. An exception will occur. This question is about the url time zone in mysql. If you don't pay attention to this trap, you may encounter it. This article mainly shares with you how to avoid the trap of url time zone in mysql.

1. ServerTimezone is not specified

Exception when configuring url in xml

<property name="url" value="jdbc:mysql://localhost:3306/mybatisstudy"/>

Caused by: com.mysql.cj.core.exceptions.InvalidConnectionAttributeException: The server time zone value '�й���׼ʱ��' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

You must configure the server or JDBC driver (via the serverTimezone configuration property), if you want to use time zone support, you need to use a more detailed time zone value.

2. Online solutions

Add parameters after the url?serverTimezone=utc

<property name="url" value="jdbc:mysql://localhost:3306/springdatastudy?serverTimezone=UTC"/>

2.1. Problems encountered

Although the time zone program added above worked without error, there was a problem when we used java code to insert the time into the database.

For example, the time inserted in the java code is: 2017-08-21 17:29:56

But the time displayed in the database is: 2017-08-21 09:29:56

3. The root cause

is because of the time zone setting problem.

UTC represents global standard time, but the time we use is the Beijing time zone, which is the East Eighth District, eight hours ahead of UTC.

UTC + (＋0800) = local (Beijing) time

4. Solution

The time zone of the url uses China Standard Time. Also serverTimezone=Asia/Shanghai

4.1 Use java code to get the local time zone id

Calendar cal = Calendar.getInstance();
TimeZone timeZone = cal.getTimeZone();
System.out.println(timeZone.getID());
System.out.println(timeZone.getDisplayName());

Asia/Shanghai
中国标准时间

Related recommendations:

MySQL or Statement usage examples

Detailed explanation of indexes and triggers in MySQL

Summary of date functions in MySQL

The above is the detailed content of Detailed explanation of how to avoid the trap of url time zone in mysql. For more information, please follow other related articles on the PHP Chinese website!