CODEWITHSUNDEEP
javamysqlmybatis
frank
frank

Reputation: 1201

mybatis convert timestamp to java date is wrong

mysql 5.7

mybatis 3.4.0

mysql-connector-java-6.0.6

There is a column named sent_datetime in a table whose type is timestamp:

enter image description here

there is a record and the value of sent_datetime is:

enter image description here

We can see that its value is 2019-06-20 17:24:34.

MySql is in a another server whose os is centos 7.

The java bean is(I set the sent_datetime as java.util.Date):

public class MailLog implements Serializable{

    private static final long serialVersionUID = 7752106961960847185L;

    private Date sent_datetime; 

    public Date getSent_datetime() {
        return sent_datetime;
    }   

}

In dao, when I query this record and print the sent_datetime value:

MailLog log = mybatisTemplate.selectOne("getMailLog", mailLog);

System.out.println(log.getSent_datetime());

However, it prints Fri Jun 21 06:24:34 CST 2019. It is wrong.

Meanwhile, if CST means American time, it should be 20th, CANNOT BE 21st, right?

If I set sent_datetime as String in java bean, then it prints correct value.

Still, some records save or show incorrect, some are correct.

Why this happens?

In mysql, I run SELECT @@global.time_zone, @@session.time_zone, @@system_time_zone;, it returns SYSTEM SYSTEM CST.

Upvotes: 1

Views: 3849

Answers (1)

0xTang
0xTang

Reputation: 886

The reason of time diff 13h is timezone definition difference between JDBC and MySQL connector.

CST has 2 definitions:

  1. Central Standard Time (USA) UTC-05:00
  2. China Standard Time UTC+08:00

In this case:

  1. JDBC considers timezone is CST-5
  2. JBDC transfers Timestamp+0 to CST-5
  3. MySQL considers timezone is CST+8，transfer CST-5 to Timestamp-13

For more details, you can reference source code in com.mysql.cj.jdbc.ConnectionImpl.initializePropsFromServer() and com.mysql.cj.jdbc.PreparedStatement.setTimestamp()

Use +08:00 instead of CST fix this issue.

set global time_zone = '+08:00';

or

jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&serverTimezone=Asia/Shanghai

Upvotes: 2

Related Questions