How to store date/time and timestamps in UTC time zone with JPA and Hibernate

Question

How can I configure JPA/Hibernate to store a date/time in the database as UTC (GMT) time zone? Consider this annotated JPA entity:

public class Event {
    @Id
    public int id;

    @Temporal(TemporalType.TIMESTAMP)
    public java.util.Date date;
}

If the date is 2008-Feb-03 9:30am Pacific Standard Time (PST), then I want the UTC time of 2008-Feb-03 5:30pm stored in the database. Likewise, when the date is retrieved from the database, I want it interpreted as UTC. So in this case 530pm is 530pm UTC. When it's displayed it will be formatted as 9:30am PST.

Solution 1

Since Hibernate 5.2, you can now force the UTC time zone by adding the following configuration property into the properties.xml JPA configuration file:

<property name="hibernate.jdbc.time_zone" value="UTC"/>

If you're using Spring Boot, then add this property to your application.properties file:

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

Solution 2

To the best of my knowledge, you need to put your entire Java app in UTC timezone (so that Hibernate will store dates in UTC), and you'll need to convert to whatever timezone desired when you display stuff (at least we do it this way).

At startup, we do:

TimeZone.setDefault(TimeZone.getTimeZone("Etc/UTC"));

And set the desired timezone to the DateFormat:

fmt.setTimeZone(TimeZone.getTimeZone("Europe/Budapest"))

Solution 3

Hibernate is ignorant of time zone stuff in Dates (because there isn't any), but it's actually the JDBC layer that's causing problems. ResultSet.getTimestamp and PreparedStatement.setTimestamp both say in their docs that they transform dates to/from the current JVM timezone by default when reading and writing from/to the database.

I came up with a solution to this in Hibernate 3.5 by subclassing org.hibernate.type.TimestampType that forces these JDBC methods to use UTC instead of the local time zone:

public class UtcTimestampType extends TimestampType {

    private static final long serialVersionUID = 8088663383676984635L;

    private static final TimeZone UTC = TimeZone.getTimeZone("UTC");

    @Override
    public Object get(ResultSet rs, String name) throws SQLException {
        return rs.getTimestamp(name, Calendar.getInstance(UTC));
    }

    @Override
    public void set(PreparedStatement st, Object value, int index) throws SQLException {
        Timestamp ts;
        if(value instanceof Timestamp) {
            ts = (Timestamp) value;
        } else {
            ts = new Timestamp(((java.util.Date) value).getTime());
        }
        st.setTimestamp(index, ts, Calendar.getInstance(UTC));
    }
}

The same thing should be done to fix TimeType and DateType if you use those types. The downside is you'll have to manually specify that these types are to be used instead of the defaults on every Date field in your POJOs (and also breaks pure JPA compatibility), unless someone knows of a more general override method.

UPDATE: Hibernate 3.6 has changed the types API. In 3.6, I wrote a class UtcTimestampTypeDescriptor to implement this.

public class UtcTimestampTypeDescriptor extends TimestampTypeDescriptor {
    public static final UtcTimestampTypeDescriptor INSTANCE = new UtcTimestampTypeDescriptor();

    private static final TimeZone UTC = TimeZone.getTimeZone("UTC");

    public <X> ValueBinder<X> getBinder(final JavaTypeDescriptor<X> javaTypeDescriptor) {
        return new BasicBinder<X>( javaTypeDescriptor, this ) {
            @Override
            protected void doBind(PreparedStatement st, X value, int index, WrapperOptions options) throws SQLException {
                st.setTimestamp( index, javaTypeDescriptor.unwrap( value, Timestamp.class, options ), Calendar.getInstance(UTC) );
            }
        };
    }

    public <X> ValueExtractor<X> getExtractor(final JavaTypeDescriptor<X> javaTypeDescriptor) {
        return new BasicExtractor<X>( javaTypeDescriptor, this ) {
            @Override
            protected X doExtract(ResultSet rs, String name, WrapperOptions options) throws SQLException {
                return javaTypeDescriptor.wrap( rs.getTimestamp( name, Calendar.getInstance(UTC) ), options );
            }
        };
    }
}

Now when the app starts, if you set TimestampTypeDescriptor.INSTANCE to an instance of UtcTimestampTypeDescriptor, all timestamps will be stored and treated as being in UTC without having to change the annotations on POJOs. [I haven't tested this yet]

Solution 4

With Spring Boot JPA, use the below code in your application.properties file and obviously you can modify timezone to your choice

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

Then in your Entity class file,

@Column
private LocalDateTime created;

Solution 5

Adding an answer that's completely based on and indebted to divestoclimb with a hint from Shaun Stone. Just wanted to spell it out in detail since it's a common problem and the solution is a bit confusing.

This is using Hibernate 4.1.4.Final, though I suspect anything after 3.6 will work.

First, create divestoclimb's UtcTimestampTypeDescriptor

public class UtcTimestampTypeDescriptor extends TimestampTypeDescriptor {
    public static final UtcTimestampTypeDescriptor INSTANCE = new UtcTimestampTypeDescriptor();

    private static final TimeZone UTC = TimeZone.getTimeZone("UTC");

    public <X> ValueBinder<X> getBinder(final JavaTypeDescriptor<X> javaTypeDescriptor) {
        return new BasicBinder<X>( javaTypeDescriptor, this ) {
            @Override
            protected void doBind(PreparedStatement st, X value, int index, WrapperOptions options) throws SQLException {
                st.setTimestamp( index, javaTypeDescriptor.unwrap( value, Timestamp.class, options ), Calendar.getInstance(UTC) );
            }
        };
    }

    public <X> ValueExtractor<X> getExtractor(final JavaTypeDescriptor<X> javaTypeDescriptor) {
        return new BasicExtractor<X>( javaTypeDescriptor, this ) {
            @Override
            protected X doExtract(ResultSet rs, String name, WrapperOptions options) throws SQLException {
                return javaTypeDescriptor.wrap( rs.getTimestamp( name, Calendar.getInstance(UTC) ), options );
            }
        };
    }
}

Then create UtcTimestampType, which uses UtcTimestampTypeDescriptor instead of TimestampTypeDescriptor as the SqlTypeDescriptor in the super constructor call but otherwise delegates everything to TimestampType:

public class UtcTimestampType
        extends AbstractSingleColumnStandardBasicType<Date>
        implements VersionType<Date>, LiteralType<Date> {
    public static final UtcTimestampType INSTANCE = new UtcTimestampType();

    public UtcTimestampType() {
        super( UtcTimestampTypeDescriptor.INSTANCE, JdbcTimestampTypeDescriptor.INSTANCE );
    }

    public String getName() {
        return TimestampType.INSTANCE.getName();
    }

    @Override
    public String[] getRegistrationKeys() {
        return TimestampType.INSTANCE.getRegistrationKeys();
    }

    public Date next(Date current, SessionImplementor session) {
        return TimestampType.INSTANCE.next(current, session);
    }

    public Date seed(SessionImplementor session) {
        return TimestampType.INSTANCE.seed(session);
    }

    public Comparator<Date> getComparator() {
        return TimestampType.INSTANCE.getComparator();        
    }

    public String objectToSQLString(Date value, Dialect dialect) throws Exception {
        return TimestampType.INSTANCE.objectToSQLString(value, dialect);
    }

    public Date fromStringValue(String xml) throws HibernateException {
        return TimestampType.INSTANCE.fromStringValue(xml);
    }
}

Finally, when you initialize your Hibernate configuration, register UtcTimestampType as a type override:

configuration.registerTypeOverride(new UtcTimestampType());

Now timestamps shouldn't be concerned with the JVM's time zone on their way to and from the database. HTH.

View more solutions

Comments

[Marc Bredt]

Shame on me! You were right and the link from your post explains it well. Now I guess my answer deserves some negative reputation :)

[codefinger]

Not at all. you encouraged me to post a very explicit example of why this is a problem.

[John K]

I was able to persist times correctly using the Calendar object so that they are stored in the DB as UTC as you suggested. However, when reading persisted entities back from the database, Hibernate assumes they're in the local time zone and the Calendar object is incorrect!

[Derek Mahar]

How do you tell Hibernate to use your custom UtcTimestampType?

[Derek Mahar]

John K, in order to resolve this Calendar read problem, I think Hibernate or JPA should provide some way to specify, for each mapping, the time zone to which Hibernate should translate the date that it reads and writes to a TIMESTAMP column.

[Derek Mahar]

divestoclimb, with which version of Hibernate is UtcTimestampType compatible?

[Derek Mahar]

"ResultSet.getTimestamp and PreparedStatement.setTimestamp both say in their docs that they transform dates to/from the current JVM timezone by default when reading and writing from/to the database." Do you have a reference? I don't see any mention of this in the Java 6 Javadocs for these methods. According to stackoverflow.com/questions/4123534/…, how these methods apply the time zone to a given Date or Timestamp is JDBC driver dependent.

[Derek Mahar]

mitchnull, your solution won't work in all cases because Hibernate delegates setting dates to the JDBC driver and each JDBC driver handles dates and time zones differently. see stackoverflow.com/questions/4123534/….

[Derek Mahar]

joekutner, after reading stackoverflow.com/questions/4123534/…, I've come to share your opinion that we should store milliseconds since the Epoch in the database rather than a Timestamp since we can't necessarily trust the JDBC driver to store dates as we would expect.

[rafa.ferreira]

But if I start my app informing to the JVM "-Duser.timezone=+00:00" property, is not the same behave?

[rafa.ferreira]

@John K - Could you explain how you could store date in UTC on db? Did you set your JVM to startup on UTC? This is the unique solution that I have found.

[divestoclimb]

I could have sworn I read that about using the JVM timezone last year, but now I can't find it. I might have found it on the docs for a specific JDBC driver and generalized.

[Shane]

As far as I can tell, it will work in all cases except when the JVM and the database server are in different time zones.

[Raedwald]

"Hibernate/JPA has a severe design deficiency" I'd say it is a deficiency in SQL, which traditionally has allowed the time-zone to be implicit, and therefore potentially anything. Silly SQL.

[Cerber]

stevekuo and @mitchnull see divestoclimb solution below which is far more better and side-effect proof stackoverflow.com/a/3430957/233906

[sleske]

Actually, instead of always storing the timezone, you can also standardize on one timezone (usually UTC), and convert everything to this timezone when persisting (and back when reading). This is what we usually do. However, JDBC does not support that directly either :-/.

[Shaun Stone]

To get the 3.6 version of your example to work i had to create a new type which was basically a wrapper around the TimeStampType then set that type on the field.

[Whome]

Do HibernateJPA support "@Factory" and "@Externalizer" annotation, it's how I do datetime utc handling in OpenJPA library. stackoverflow.com/questions/10819862/…

[Aubergine]

Would be great to see the solution for JPA and Spring Configuration.

[Nigel]

A note regarding using this approach with native queries within Hibernate. To have these overridden types be used you must set the value with query.setParameter(int pos, Object value), not query.setParameter(int pos, Date value, TemporalType temporalType). If you use the latter then Hibernate will use its original type implementations, as they are hard coded.

[Stony]

Where should I call the statement configuration.registerTypeOverride(new UtcTimestampType()); ?

[Shane]

@Stony Wherever you initialize your Hibernate configuration. If you have a HibernateUtil (most do), it'll be in there.

[fl4l]

@divestoclimb, I have an application with Hibernate 3.6. I tried TimestampTypeDescriptor, how can I apply that descriptor to a single entity field? I tried to map my entity with @Type(type="path.to.UtcTimestampTypeDescriptor") but throws an SQLGrammarException: could not execute query

[krzakov]

any chance to apply this on hibernate integrated with spring data jpa?

[Lukasz Frankowski]

It works, but after checking it I realized that it's not required for postgres server working in timezone="UTC" and with all default types of timestamps as "timestamp with time zone" (it's done automatically then). But, here is fixed version for Hibernate 4.3.5 GA as complete single class and overridding spring factory bean pastebin.com/tT4ACXn6

[rohith]

one way i could get this to work is by adding @Type(type="xx.xx.UtcTimestampType") on the Date field. I am not sure how to register this new type with configuration.

[Mike Stoddart]

On Hibernate 4.3.11.Final, your UtcTimestampTypeDescriptor class has two compilation errors: "The type new BasicExtractor<X>(){} must implement the inherited abstract method BasicExtractor<X>.doExtract(CallableStatement, int, WrapperOptions) UtcTimestampTypeDescriptor.java" and "The type new BasicExtractor<X>(){} must implement the inherited abstract method BasicExtractor<X>.doExtract(CallableStatement, String, WrapperOptions)". Apologies if this comment isn't too readable.

[rohith]

Also, you could use @TypeDef to avoid hard coding of class name. If you think this type is used across multiple entity classes in the same package, then create package-info.java like discussed here: vard-lokkur.blogspot.com/2013/04/…

[Vlad Mihalcea]

I wrote that one too :D Now, guess who added support for this feature in Hibernate?

[Dinei]

Oh, just now I realized the name and picture are the same in your profile and those articles... Good job Vlad :)

[Tony Chemit]

This does not work when jvm does not use the same time zone as told by @Shane. The simple next response solve all the pb.

[The Coder]

@VladMihalcea if it's for Mysql, one need to tell MySql to use timezone by using useTimezone=true in the connection string. Then only setting property hibernate.jdbc.time_zone will work

[Vlad Mihalcea]

Actually, you need to set useLegacyDatetimeCode to false

[Alex R]

hibernate.jdbc.time_zone appears to be ignored or has no effect when used with PostgreSQL

[Vlad Mihalcea]

Check out my High-Performance Java Persistence GitHub repositor. There's a test case you can run on PostgreSQL and shows that the property works just fine.

[muasif80]

I stored just a date 1985-06-01 in column type of date in postgres. When I retrieve it it gives me 1985-05-31. The timezone are EST for Java and Postgres. Does it mean jpa automatically converts the date using EST?

[muasif80]

If I set this property timezone to utc what would happen to the existing data?

[Vlad Mihalcea]

Adding this to a legacy product requires evaluating whether the existing timestamps won't drift. So, it all depends on how you saved data previously.

[Mr. Anderson]

If you use Quarkus, you can make use of quarkus property directly.

[stranger]

Also add this in ur properties.file to make sure your db entries also happen in UTC spring.jpa.properties.hibernate.jdbc.time_zone=UTC

[tiennv]

It's work for me with ``` private Date lastUpdatedAt; ```

[Josean Muñoz]

I love you so much