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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

There are several timezones in operation here    Java s Date classes  util and sql   which have implicit timezones of UTC  The timezone your JVM is running in  and the default timezone of your database server    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

User · Answer

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  lt X gt  ValueBinder lt X gt  getBinder final JavaTypeDescriptor lt X gt  javaTypeDescriptor            return new BasicBinder lt X gt   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  lt X gt  ValueExtractor lt X gt  getExtractor final JavaTypeDescriptor lt X gt  javaTypeDescriptor            return new BasicExtractor lt X gt   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 lt Date gt          implements VersionType lt Date gt   LiteralType lt Date gt        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 lt Date gt  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

User · Answer

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

User · Answer

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  lt      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

User · Answer

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

User · Answer

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  lt      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

User · Answer

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  lt X gt  ValueBinder lt X gt  getBinder final JavaTypeDescriptor lt X gt  javaTypeDescriptor            return new BasicBinder lt X gt   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  lt X gt  ValueExtractor lt X gt  getExtractor final JavaTypeDescriptor lt X gt  javaTypeDescriptor            return new BasicExtractor lt X gt   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

User · Answer

With Hibernate 5 2  you can now force the UTC time zone using the following configuration property   lt property name  quot hibernate jdbc time zone quot  value  quot UTC quot   gt

User · Answer

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  lt - gt  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    lt dependency gt       lt groupId gt com montrosesoftware lt  groupId gt       lt artifactId gt DbAssist-5 2 2 lt  artifactId gt       lt version gt 1 0-RELEASE lt  version gt   lt  dependency gt    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

User · Answer

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  lt X gt  ValueBinder lt X gt  getBinder final JavaTypeDescriptor lt X gt  javaTypeDescriptor            return new BasicBinder lt X gt   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  lt X gt  ValueExtractor lt X gt  getExtractor final JavaTypeDescriptor lt X gt  javaTypeDescriptor            return new BasicExtractor lt X gt   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

User · Answer

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  lt - gt  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    lt dependency gt       lt groupId gt com montrosesoftware lt  groupId gt       lt artifactId gt DbAssist-5 2 2 lt  artifactId gt       lt version gt 1 0-RELEASE lt  version gt   lt  dependency gt    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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

There are several timezones in operation here    Java s Date classes  util and sql   which have implicit timezones of UTC  The timezone your JVM is running in  and the default timezone of your database server    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

User · Answer

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

User · Answer

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

User · Answer

With Hibernate 5 2  you can now force the UTC time zone using the following configuration property   lt property name  quot hibernate jdbc time zone quot  value  quot UTC quot   gt

User · Answer

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  lt X gt  ValueBinder lt X gt  getBinder final JavaTypeDescriptor lt X gt  javaTypeDescriptor            return new BasicBinder lt X gt   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  lt X gt  ValueExtractor lt X gt  getExtractor final JavaTypeDescriptor lt X gt  javaTypeDescriptor            return new BasicExtractor lt X gt   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 lt Date gt          implements VersionType lt Date gt   LiteralType lt Date gt        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 lt Date gt  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

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

Examples related to java

Examples related to hibernate

Examples related to datetime

Examples related to jpa

Examples related to timezone