The date is right in the application, but wrong in mysql [time zone]

Question

Problem: The correct time on the application server is incorrect in the database.

I am in China, Timezone is UTC + 8. I use sleep mode. Define an entity as follows (language: Scala)

class CargoJournal { @Type(`type`="org.jadira.usertype.dateandtime.joda.PersistentLocalDateTime") var deliverTime: LocalDateTime = _ @Temporal(TemporalType.TIMESTAMP) @Column(nullable=false) var logDate:Date = _ } 

I open the sleep log as shown on my application server. Current time on Thu Sep 13 11:08:44 CST 2012

 insert into wms_history_cargo_journal (deliver_time, log_date) binding parameter [1] as [TIMESTAMP] - 2012-09-13 11:08:44.25 binding parameter [2] as [TIMESTAMP] - Thu Sep 13 11:08:44 CST 2012 

In my database server:

 mysql> select timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00')); +----------------------------------------------------------------+ | timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00')) | +----------------------------------------------------------------+ | 08:00:00 | +----------------------------------------------------------------+ 

So, mysql timezone is right. UTC + 8

After choosing from mysql:

 mysql> SELECT deliver_time, log_date FROM wms_history_cargo_journal; +---------------------+---------------------+ | deliver_time | log_date | +---------------------+---------------------+ | 2012-09-13 11:08:44 | 2012-09-13 03:08:44 | +---------------------+---------------------+ 

Error log_date!

Answer 1

What are the types of columns in MySQL? I suspect DATETIME. This type does not store a “point in time”, it stores a “hour on a clock”, therefore it can mean a different moment in different time zones.

When the MySQL driver writes java.util.Date to the DATETIME column, it must select some time zone to write “hours on hours,” because the same java.util.Date can mean a different hour in different time zones. It stores the clock, as in the local time zone of the MySQL server.

LocalDateTime does not have this problem because it is similar to DATETIME. It represents the hour on the clock, not the point in time, so the year / month / day-hour / minute / second is simply stored in the database. The notification in the LocalDateTime hibernation log is set as is, while there is a time zone ("CST") next to Date.

It is generally recommended that you always store time in UTC, so use a DateTime, not a Date or LocalDateTime. DateTime with jadira converter always saves / reads DATETIME as UTC.