Hibernate retrieve timestamps in UTC

Question

I am using hibernate + spring and want to store/load timestamps in UTC. I've read that I should add a property, so I added this to my application.properties

spring.jpa.properties[hibernate.jdbc.time_zone]=UTC

This worked for one part of the problem - now dates are saved in utc in the database. But when I retrieve timestamps, they are transformed into default timezone. How can I fix this without setting default time zone to UTC? The property of the entity has type LocalDateTime. I ran the code, and noticed that the proper result set method is used during get(the one that accepts calendar) with instance that has zone info storing UTC. But after setting calendar's values to the one retrieved from the database, the calendar is transformed into Timestamp with this code

Timestamp ts = new Timestamp(c.getTimeInMillis());

In debug mode, I see that ts stores cdate field with value of timestamp in default time zone(not UTC).

Answer 1

First of all, if we are talking about Hibernate 5 (5.2.3 - 5.6.x if to be precise) the purpose of hibernate.jdbc.time_zone setting is not to give the ability for application developer to implement some kind of sophisticated date/time logic, but to synchronize persistence provider with underlying database, that is clearly stated in the corresponding CR:

Currently my database has implicit date times in UTC. No zoned data is appended to the end of the string (e.g. "2013-10-14 04:00:00"). When Hibernate reads this as a ZonedDateTime, it incorrectly reads it in as EST, as that is the TimeZone of the JVM. It would be nice to be able to specify the TimeZone of a field by an annotation perhaps.

basically: you definitely need to set up hibernate.jdbc.time_zone if (mine: and only if) SQL statement like SELECT SYSDATE FROM DUAL (SELECT LOCALTIMESTAMP for PostgreSQL, etc) returns something, what you do not expect, in that case Hibernate will start adjusting non-timezone-aware JDBC data to something more or less reliable for application - that is exactly what you are observing (when I retrieve timestamps, they are transformed into default timezone)

At second, any speculations around JSR-310 and JDBC 4.2 (like for timezone-aware java types you need to define DB columns as timestamp with time zone), are not correct in case of Hibernate 5, that is mentioned in the corresponding CR as well:

The whole idea of "stored TZ" really depends on how the database/driver treats TIMESTAMP and whether it supports a "TIMESTAMP_WITH_TIMEZONE" type. I personally think it is a huge mistake to save the specific TZ differences to the DB, so I would personally continue to not support TIMESTAMP_WITH_TIMEZONE types. This would mean we never have to bind the Calendar because we could simply convert the value to to the JVM/JDBC TZ ourselves. Specifically I would suggest that we (continue to) assume that the driver has been set up such that the same TZ is used when ...

And indeed, if you try to find usage of java.sql.Types#TIMESTAMP_WITH_TIMEZONE in Hibernate 5 sources you will find nothing, just because that time Hibernate developers didn't get a common opinion about how timezone conversions should work in cases of different Java versions, Java types, DB engines and JDBC drivers (they are developing the most popular (mine: the only one) JPA implementation, that is definitely not the same as develop microservice), however, there are a lot of related changes in Hibernate 6 (check TimeZoneStorageType for example). In Hibernate 5 all timezone conversion logic passes through TimestampTypeDescriptor:

@Override
protected X doExtract(ResultSet rs, String name, WrapperOptions options) throws SQLException {
  return options.getJdbcTimeZone() != null ?
   javaTypeDescriptor.wrap( rs.getTimestamp( name, Calendar.getInstance( options.getJdbcTimeZone() ) ), options ) :
   javaTypeDescriptor.wrap( rs.getTimestamp( name ), options );
}

and as you can see, Hibernate 5 just gives a hint to JDBC driver, how the last should process #getTimestamp call:

Retrieves the value of a JDBC TIMESTAMP parameter as a java.sql.Timestamp object, using the given Calendar object to construct the Timestamp object. With a Calendar object, the driver can calculate the timestamp taking into account a custom timezone and locale. If no Calendar object is specified, the driver uses the default timezone and locale.

in regard to your case:

you either need to use timezone-aware java types (ZonedDateTime/OffsetDateTime, or even Instant) or code your own Hibernate type, which will handle timezone conversions - that is not so hard as it might seem.

Answer 2

we can also set it up` per-session basis:

session = HibernateUtil.getSessionFactory().withOptions()
  .jdbcTimeZone(TimeZone.getTimeZone("UTC"))
  .openSession();

Answer 3

My database timezone was in UTC and in my application timezone I solved this problem by having both the Entity and the table have a date in UTC so that there will need to be no conversion between them. Then I did the conversions between timestamps in code in the getters and setters. Then I think you do it manually.

Setter and getter for that field:

public void setCreatedDate(LocalDateTime createdAt)
{         
    this.createdAt = createdAt.atZone(ZoneId.systemDefault()).withZoneSameInstant(ZoneOffset.UTC).toLocalDateTime();
}

public LocalDateTime getCreatedDate()
{         
    return createdAt.atZone(ZoneId.systemDefault()).withZoneSameInstant(ZoneOffset.UTC).toLocalDateTime();
}

Answer 4

As alluded to be Andrey in his answer, in Hibernate 6 the way to normalize dates/times to UTC is to use the java.time types OffsetDateTime or ZonedDateTime, and either:

• annotate the field @TimeZoneStorage(NORMALIZE_UTC), or
• set the property hibernate.timezone.default_storage=NORMALIZE_UTC.

I'm not very certain what you mean about using LocalDateTime here. What would it even mean to normalize a local datetime to UTC? That statement just doesn't really make sense: you can't move a local datetime to a new time zone because it doesn't have an associated time zone to begin with.

I think what you mean is that your "local" date times are actually zoned datetimes in the current JVM time zone. But if that's the case, it's very easy to use localDateTime.atZone(ZoneId.systemDefault()) to represent that situation correctly.

Answer 5

Just use Instant (Hibernate understands this). It is as simple as this in your entity class:

@Column(name="time_when_something_happened", columnDefinition="TIMESTAMP")
private Instant timeWhenSomethingHappened;

That's all. No need to set the spring.jpa.properties.hibernate.jdbc.time_zone=UTC property as Hibernate knows it is implied since you are using Instant.

Tested with Hibernate 6.2, Spring Boot 3.1 and PostgreSQL 11.

Here is why you should (presumably) be using Instant: For most applications the requirement is to record the timestamp of something which has happened. Note: past tense. So that would be the Instant type in Java, not LocalDateTime or OffsetDateTime.

Your application in presumably something backend related?. And persistence is an implementation detail seen from the point of view of your end-users. In other words: Using for example OffsetDateTime really makes no sense as there is no point in carrying around such information as an offset. Just defer the decision on how you want to present the timestamp value until you are actually faced with a human user, as in a UI. Bottom line: it is Instant you want in your persistence layer! (*)

*) Perhaps a few words on why you see so many SO and Blog answers directing you towards using java type LocalDateTime or OffsetDateTime when real need is indeed to record the time when something happened: it is because the JPA standard (until lately) did not mention Instant. For a long time using Instant without further ado like I advise has been possible but it has been a Hibernate specialty. The issue (I would call it an omission) was logged in the JPA Spec back in 2017 but is only now, Aug 2023, seeing some action. So indeed it will be part of the JPA spec going forward, perhaps in v3.2. With this in mind I definitely think you should be using Instant : it is simple, and it conveys intent.