How to avoid the trap of URL time zone in MySQL

Preface

Recently, when using MySQL 6.0.x or higher jar, you need to specify serverTimezone in the code URL link. An exception will occur:

1. serverTimezone not specified

Configure the URL in xml

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

Abnormalities

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) to use a more detailed time zone value if you want to use time zone support.

2. Online solutions

Add the parameter ?serverTimezone=utc after the url

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

2.1. Problems encountered

Although there is no error in adding the time zone program above, there is a problem when we insert the time into the database using Java code.

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

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

3. Root Cause

Because of the time zone setting problem.

UTC stands for Universal Coordinated Time, but the time we use is the Beijing time zone, which is East 8, which is eight hours ahead of UTC.

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

4. Solution

The time zone of the URL uses China Standard Time. That is, serverTimezone=Asia/Shanghai

4.1 Use Java code to obtain 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
China Standard Time

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.