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.
You would think this common problem would be taken care of by Hibernate. But its not! There are a few "hacks" to get it right.
The one I use is to store the Date as a Long in the database. So I am always working with milliseconds after 1/1/70. I then have getters and setters on my Class that return/accept only Dates. So the API remains the same. The down side is that I have longs in the database. SO with SQL I can pretty much only do <,>,= comparisons -- not fancy date operators.
Another approach is to user a custom mapping type as described here: http://www.hibernate.org/100.html
I think the correct way to deal with this is to use a Calendar instead of a Date though. With the Calendar you can set the TimeZone before persisting.
NOTE: Silly stackoverflow won't let me comment, so here is a response to david a.
If you create this object in Chicago:
new Date(0);
Hibernate persists it as "12/31/1969 18:00:00". Dates should be devoid of timezone, so I'm not sure why the adjustment would be made.
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;
You would think this common problem would be taken care of by Hibernate. But its not! There are a few "hacks" to get it right.
The one I use is to store the Date as a Long in the database. So I am always working with milliseconds after 1/1/70. I then have getters and setters on my Class that return/accept only Dates. So the API remains the same. The down side is that I have longs in the database. SO with SQL I can pretty much only do <,>,= comparisons -- not fancy date operators.
Another approach is to user a custom mapping type as described here: http://www.hibernate.org/100.html
I think the correct way to deal with this is to use a Calendar instead of a Date though. With the Calendar you can set the TimeZone before persisting.
NOTE: Silly stackoverflow won't let me comment, so here is a response to david a.
If you create this object in Chicago:
new Date(0);
Hibernate persists it as "12/31/1969 18:00:00". Dates should be devoid of timezone, so I'm not sure why the adjustment would be made.
Hibernate does not allow for specifying time zones by annotation or any other means. If you use Calendar instead of date, you can implement a workaround using HIbernate property AccessType and implementing the mapping yourself. The more advanced solution is to implement a custom UserType to map your Date or Calendar. Both solutions are explained in my blog post here: http://www.joobik.com/2010/11/mapping-dates-and-time-zones-with.html
I encountered just the same problem when I wanted to store the dates in the DB as UTC and avoid using varchar and explicit String <-> java.util.Date conversions, or setting my whole Java app in the UTC time zone (because this could lead to another unexpected issues, if the JVM is shared across many applications).
So, there is an open source project DbAssist, which allows you to easily fix the read/write as UTC date from the database. Since you are using JPA Annotations to map the fields in the entity, all you have to do is to include the following dependency to your Maven pom file:
<dependency>
<groupId>com.montrosesoftware</groupId>
<artifactId>DbAssist-5.2.2</artifactId>
<version>1.0-RELEASE</version>
</dependency>
Then you apply the fix (for Hibernate + Spring Boot example) by adding @EnableAutoConfiguration annotation before the Spring application class. For other setups installation instructions and more use examples, just refer to the project's github.
The good thing is that you don't have to modify the entities at all; you can leave their java.util.Date fields as they are.
5.2.2 has to correspond to the Hibernate version you are using. I am not sure, which version you are using in your project, but the full list of provided fixes is available on the wiki page of the project's github. The reason why the fix is different for various Hibernate versions is because Hibernate creators changed the API a couple of times between the releases.
Internally, the fix uses hints from divestoclimb, Shane and a few other sources in order to create a custom UtcDateType. Then it maps the standard java.util.Date with the custom UtcDateType which handles all the necessary time zone handling.
The mapping of the types is achieved using @Typedef annotation in the provided package-info.java file.
@TypeDef(name = "UtcDateType", defaultForType = Date.class, typeClass = UtcDateType.class),
package com.montrosesoftware.dbassist.types;
You can find an article here which explains why such a time shift occurs at all and what are the approaches to solve it.
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]
There are several timezones in operation here:
All of these can be different. Hibernate/JPA has a severe design deficiency in that a user cannot easily ensure that timezone information is preserved in the database server (which allows reconstruction of correct times and dates in the JVM).
Without the ability to (easily) store timezone using JPA/Hibernate then information is lost and once information is lost it becomes expensive to construct it (if at all possible).
I would argue that it is better to always store timezone information (should be the default) and users should then have the optional ability to optimize the timezone away (although it only really affects display, there is still an implicit timezone in any date).
Sorry, this post doesn't provide a work-around (that's been answered elsewhere) but it is a rationalization of why always storing timezone information around is important. Unfortunately it seems many Computer Scientists and programming practitioners argue against the need for timezones simply because they don't appreciate the "loss of information" perspective and how that makes things like internationalization very difficult - which is very important these days with web sites accessible by clients and people in your organization as they move around the world.
Date is not in any time zone (it is a millisecond office from a defined moment in time same for everyone), but underlying (R)DBs generally store timestamps in political format (year, month, day, hour, minute, second, ...) that is time-zone sensitive.
To be serious, Hibernate MUST be allow being told within some form of mapping that the DB date is in such-and-such timezone so that when it loads or stores it it does not assume its own...
There are several timezones in operation here:
All of these can be different. Hibernate/JPA has a severe design deficiency in that a user cannot easily ensure that timezone information is preserved in the database server (which allows reconstruction of correct times and dates in the JVM).
Without the ability to (easily) store timezone using JPA/Hibernate then information is lost and once information is lost it becomes expensive to construct it (if at all possible).
I would argue that it is better to always store timezone information (should be the default) and users should then have the optional ability to optimize the timezone away (although it only really affects display, there is still an implicit timezone in any date).
Sorry, this post doesn't provide a work-around (that's been answered elsewhere) but it is a rationalization of why always storing timezone information around is important. Unfortunately it seems many Computer Scientists and programming practitioners argue against the need for timezones simply because they don't appreciate the "loss of information" perspective and how that makes things like internationalization very difficult - which is very important these days with web sites accessible by clients and people in your organization as they move around the world.
Please take a look at my project on Sourceforge which has user types for standard SQL Date and Time types as well as JSR 310 and Joda Time. All of the types try to address the offsetting issue. See http://sourceforge.net/projects/usertype/
EDIT: In response to Derek Mahar's question attached to this comment:
"Chris, do your user types work with Hibernate 3 or greater? – Derek Mahar Nov 7 '10 at 12:30"
Yes these types support Hibernate 3.x versions including Hibernate 3.6.
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]
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"))
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.
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"))
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.
Hibernate does not allow for specifying time zones by annotation or any other means. If you use Calendar instead of date, you can implement a workaround using HIbernate property AccessType and implementing the mapping yourself. The more advanced solution is to implement a custom UserType to map your Date or Calendar. Both solutions are explained in my blog post here: http://www.joobik.com/2010/11/mapping-dates-and-time-zones-with.html
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"))
Date is not in any time zone (it is a millisecond office from a defined moment in time same for everyone), but underlying (R)DBs generally store timestamps in political format (year, month, day, hour, minute, second, ...) that is time-zone sensitive.
To be serious, Hibernate MUST be allow being told within some form of mapping that the DB date is in such-and-such timezone so that when it loads or stores it it does not assume its own...
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;
Please take a look at my project on Sourceforge which has user types for standard SQL Date and Time types as well as JSR 310 and Joda Time. All of the types try to address the offsetting issue. See http://sourceforge.net/projects/usertype/
EDIT: In response to Derek Mahar's question attached to this comment:
"Chris, do your user types work with Hibernate 3 or greater? – Derek Mahar Nov 7 '10 at 12:30"
Yes these types support Hibernate 3.x versions including Hibernate 3.6.
I encountered just the same problem when I wanted to store the dates in the DB as UTC and avoid using varchar and explicit String <-> java.util.Date conversions, or setting my whole Java app in the UTC time zone (because this could lead to another unexpected issues, if the JVM is shared across many applications).
So, there is an open source project DbAssist, which allows you to easily fix the read/write as UTC date from the database. Since you are using JPA Annotations to map the fields in the entity, all you have to do is to include the following dependency to your Maven pom file:
<dependency>
<groupId>com.montrosesoftware</groupId>
<artifactId>DbAssist-5.2.2</artifactId>
<version>1.0-RELEASE</version>
</dependency>
Then you apply the fix (for Hibernate + Spring Boot example) by adding @EnableAutoConfiguration annotation before the Spring application class. For other setups installation instructions and more use examples, just refer to the project's github.
The good thing is that you don't have to modify the entities at all; you can leave their java.util.Date fields as they are.
5.2.2 has to correspond to the Hibernate version you are using. I am not sure, which version you are using in your project, but the full list of provided fixes is available on the wiki page of the project's github. The reason why the fix is different for various Hibernate versions is because Hibernate creators changed the API a couple of times between the releases.
Internally, the fix uses hints from divestoclimb, Shane and a few other sources in order to create a custom UtcDateType. Then it maps the standard java.util.Date with the custom UtcDateType which handles all the necessary time zone handling.
The mapping of the types is achieved using @Typedef annotation in the provided package-info.java file.
@TypeDef(name = "UtcDateType", defaultForType = Date.class, typeClass = UtcDateType.class),
package com.montrosesoftware.dbassist.types;
You can find an article here which explains why such a time shift occurs at all and what are the approaches to solve it.
Source: Stackoverflow.com