How to Avoid the Trap of url Time Zone in mysql

Preface

Recently, when using jar above 6.0. x of mysql, you need to specify serverTimezone in the link of code url. An exception occurs:

1. serverTimezone not specified

url is configured in xml

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

Exceptions that occur

Caused by: com.mysql.cj.core.exceptions.InvalidConnectionAttributeException: The server time zone value '� SG ���׼ʱ��' 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 serverTimezone configuration properties), and if you want to use time zone support, you need to use a more detailed time zone value.

2. Online Solutions

Add parameters after url? serverTimezone = utc

<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, we have a problem when inserting the database time with java code.

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

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

3. Root causes

Because of the time zone setting problem.

UTC represents the global standard time, but the time we use is Beijing time zone, that is, East Zone 8, which is 8 hours ahead of UTC.

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

4. Solutions

The time zone of url uses China Standard Time. It is also just serverTimezone=Asia/Shanghai

4.1 Get the local time zone id using the java code

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

Asia/Shanghai
 China Standard Time 

Summarize