MySQL/Hibernate: getting value +1H on insert type Time

0
votes

I'm working on a java web project that uses:

  • Hibernate 5.2.2/JPA 2.0+ MySQL5InnoDBDialect

  • MySQL 5.6.15-innoDB (on EasyPHP/PHPMyAdmin) + JDBC connector 6.0.4

  • Joda time API 2.9.4 + Fasterxml jackson API 2.8.3

I'm facing a problem on inserting Time data on database. everytime i put a row, i get a +1H value on time column!

  • Attribute on Java:

@JsonFormat(shape=JsonFormat.Shape.STRING, pattern="HH:mm")
@Column(name = "RES_DUREE", nullable = false)
@Temporal(TemporalType.TIME) private Date resDuree;

  • Attribute on SQL:

RES_DUREE TIME NOT NULL;

EDIT (After Adrian Shum's Comment):

  • Connection line:

jdbc.url = jdbc:mysql://localhost:3306/mydb?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

I do use UTC but it still 1H+ .
Any suggestion will help, thanks.

1
mysqlsqlhibernatejakarta-eetime
usually caused by mismatched timezone between app server and DB. Ever tried to trace the actual SQL sent to DB? – Adrian Shum
No, please how can i do trace it? and why do i? and thanks. – Malek Boubakri
because it give you some hints on what is the actual data sent to DB (and retrieved). Tools like JdbcDsLog (disclaimer, I am maintaining a fork of this at github.com/adrianshum/jdbcdslog) or similar tools will work. Though your connection string claimed it is UTC, is your app server and the DB server itself in UTC? – Adrian Shum
Yes, i think the problem is in the server time config. it is set by default to Paris instead off UTC. If you can write an answer even theoretical one and mention your useful tool may someone use it later and we will be grateful. – Malek Boubakri

1 Answers

1
votes

Normally it is caused by server and DB time zone mismatch.

In brief, java.util.Date does not contain Timezone information. Conceptually you can treat it as simply representing Date + Time (similar to what JODA/JSR310 LocalDateTime is doing). Therefore if your app server is UTC+10, and your DB is UTC+2, when you save a date of "2016-10-13 10:11:12", although your app server is treating it as "2016-10-13 10:11:12 +10:00", it is simply passing "2016-10-13 10:11:12" to DB. Given DB is UTC+2, it is thinking the time actually means "2016-10-13 10:11:12 +02:00". Situation become more messy if your JDBC connection is claimed to be "UTC+10", most DB is going to "smartly" translate "2016-10-13 10:11:12 +02:00" to "2016-10-13 18:11:12 +10:00" which caused weird time stored and retrieved.

You may diagnose by tracing the SQL (and the actual value used) for corresponding inserts and select. You should see discrepancies between the values, vs the value stored in table. Such tracing can be done by misc way, e.g.

  1. Older version of Hibernate can show the parameter used in prepared statement by turning on proper logger
  2. You may use tools like JdbcDsLog (Disclaimer: I am maintainer for a fork of JbdcDsLog at http://github.com/adrianshum/jdbcdslog)
  3. There is probably tools in DBMS side to trace incoming queries.

Best way to solve is to make sure everything is in the same timezone, and the most rational choice for timezone is UTC.