Copying TIMESTAMP to DATETIME on MySQL with Hibernate

Question

I have these two classes

class Source {
    // mapped to TIMESTAMP
    @Version
    @Column(columnDefinition="TIMESTAMP(3) DEFAULT '2016-01-01'")
    Instant myInstant;
}

class Destination {
    // mapped to DATETIME
    @Basic(optional=true)
    Instant myInstant;
}

When using Hibernate, I assign

destination.myInstant = source.myInstant;

and then the stored value is smaller by one hour than the original - both according to the command line MySQL client and in Java. My current timezone is UTC+1, so the reason is obviously a timezone conversion.

There are a few places where this can be fixed, but I'm looking for the best practice. The server should work world-wide, so it should continue to use UTC internally, right?

Should I just change the column type to TIMESTAMP? Then, why does Instant by default map to DATETIME?

---

According to this article, Instant does map to TIMESTAMP, but in my case it did not. Why?

Answer 1

If you want to work with timezones and Java 8 I would recommend using ZonedDateTime or OffsetTimeZone (the latter being prefered when working with Hibernate). For older versions use Calendar.

• When you instance it should go by default with the timezone of your computer.
• Check if the database is timestamp with or without timezone.
• The default you set is also without timezone, and if it is "with timezone" it should automatically add the database's offset.

I hope some of this works. Here's how I did in one of my projects.

@Column(name = "registration_time")
private OffsetDateTime registrationTime;
[...]
subscriber.setRegistrationTime(OffsetDateTime.now());

Answer 2

In MySQL 5 & above, TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval. This occurs only for the TIMESTAMP data type but not for DATETIME. This is the reason you are seeing the difference while assigning a TIMESTAMP to DATETIME. So, having both the columns of same type should work. Hibernate by default maps InstantType to database TIMESTAMP type. Though you could use it for both TIMESTAMP and DATETIME in MYSQL, they are handled differently.