Hibernate subtracting an hour when persisting Joda DateTime

stephaneeybert · **Joined:** Mon Apr 16, 2007 8:10 am **Posts:** 246

Hi,

I'm using a Joda DateTime as a property in a domain bean.

When Hibernate is persisting the bean, the hour of the DateTime property is subtracted 1 or 2 hours, as can be seen in the Hibernate debug log:

Quote:

Hibernate:
call identity()
2011-03-17 09:59:15,721 DEBUG [HibernateTransactionManager] Found thread-bound Session [org.hibernate.impl.SessionImpl@508be] for Hibernate transaction
2011-03-17 09:59:15,724 DEBUG [HibernateTransactionManager] Participating in existing transaction
2011-03-17 09:59:15,724 DEBUG [StatisticsVisit] ========================>>> Getting: 2009-03-17T09:59:04.794+01:00
Hibernate:
insert
into
statistics_visit
(version, visit_datetime, visitor_host_address, visitor_browser, visitor_referer, id)
values
(?, ?, ?, ?, ?, null)
2011-03-17 09:59:15,725 TRACE [BasicBinder] binding parameter [1] as [INTEGER] - 0
2011-03-17 09:59:15,725 TRACE [BasicBinder] binding parameter [2] as [TIMESTAMP] - 2009-03-17 08:59:04.794
2011-03-17 09:59:15,726 TRACE [BasicBinder] binding parameter [3] as [VARCHAR] - 123.22.454.232
2011-03-17 09:59:15,726 TRACE [BasicBinder] binding parameter [4] as [VARCHAR] - Chrome
2011-03-17 09:59:15,726 TRACE [BasicBinder] binding parameter [5] as [VARCHAR] - http://www.francofil.com

The bean getter debug logger shows 09:59:04 but Hibernate inserts with 08:59:04

Why is Hibernate inserting with a different hour ?

Is there any time zone setup somewhere in the way ?

Here is the dateTime typedef:

Code:

   <typedef name="dateTime" class="org.jadira.usertype.dateandtime.joda.PersistentDateTime" />

Here is the Hibernate mapping:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="com.thalasoft.learnintouch.core.domain.StatisticsVisit" table="statistics_visit" dynamic-insert="true" dynamic-update="true">
        <id name="id" type="java.lang.Integer">
            <column name="id" />
            <generator class="identity" />
        </id>
        <version name="version" type="int">
            <column name="version" not-null="true" />
        </version>
        <property name="visitDateTime" type="dateTime">
            <column name="visit_datetime" not-null="true" />
        </property>
        <property name="visitorHostAddress" type="string">
            <column name="visitor_host_address" not-null="true" />
        </property>
        <property name="visitorBrowser" type="string">
            <column name="visitor_browser" not-null="true" />
        </property>
        <property name="visitorReferer" type="string">
            <column name="visitor_referer" not-null="true" />
        </property>
    </class>
</hibernate-mapping>

Here is the domain bean:

Code:

package com.thalasoft.learnintouch.core.domain;

import org.joda.time.DateTime;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class StatisticsVisit implements java.io.Serializable {

   private static Logger logger = LoggerFactory.getLogger(StatisticsVisit.class);

   private Integer id;
   private int version;
   private DateTime visitDateTime;
   private String visitorHostAddress;
   private String visitorBrowser;
   private String visitorReferer;

   public StatisticsVisit() {
   }

   public Integer getId() {
      return this.id;
   }

   public void setId(Integer id) {
      this.id = id;
   }

   public int getVersion() {
      return this.version;
   }

   public void setVersion(int version) {
      this.version = version;
   }

   public DateTime getVisitDateTime() {
      logger.debug("========================>>> Getting: " + this.visitDateTime.toString());
      return this.visitDateTime;
   }

   public void setVisitDateTime(DateTime visitDateTime) {
      logger.debug("========================>>> Setting: " + visitDateTime.toString());
      this.visitDateTime = visitDateTime;
   }

   public String getVisitorHostAddress() {
      return this.visitorHostAddress;
   }

   public void setVisitorHostAddress(String visitorHostAddress) {
      this.visitorHostAddress = visitorHostAddress;
   }

   public String getVisitorBrowser() {
      return this.visitorBrowser;
   }

   public void setVisitorBrowser(String visitorBrowser) {
      this.visitorBrowser = visitorBrowser;
   }

   public String getVisitorReferer() {
      return this.visitorReferer;
   }

   public void setVisitorReferer(String visitorReferer) {
      this.visitorReferer = visitorReferer;
   }

}

stephaneeybert · **Joined:** Mon Apr 16, 2007 8:10 am **Posts:** 246

Anyone ?

stephaneeybert · **Joined:** Mon Apr 16, 2007 8:10 am **Posts:** 246

I noticed over time that this forum is the least active one I've seen.

Is there another Hibernate forum we should post on ? Or is Hibernate hibernating or dying ?

sanne.grinovero · **Posted:** Fri Mar 18, 2011 6:43 am

Quote:

I noticed over time that this forum is the least active one I've seen.

Is there another Hibernate forum we should post on ? Or is Hibernate hibernating or dying ?

Lol, the opposite is more likely. We're just too busy, and need to rely on users helping each other. I can't read all posts, definitely not answer them all, but I'm sorry I would like to.

Back to your problem:
Hibernate doesn't do anything to your dates: just nothing, what you give it, it will insert that. You most likely are having a rendering issue of this date you're logging: remember a logger will transform a Date object into a String, and this is done using the timezone your JVM is being configured with.
Nothing to do with Hibernate, this is about Java's DATE issues: so I agree it's a good idea to use JodaTime, but then I'd suggest you to ask to the JodaTime experts/forums.
Remember your JVM timezone can be explicitly set, or it can use what your operating system is telling, and you also might wish to check the timezone settings on your database.

stephaneeybert · **Joined:** Mon Apr 16, 2007 8:10 am **Posts:** 246

Thanks Sanne, that was an awesome answer, I owe you !

I understand you guys hard core techies have other things to do than to support newbies..

You come to Estonia I'll buy you a beer :-)

sanne.grinovero · **Posted:** Fri Mar 18, 2011 8:37 am

thanks :)
let me know if you solve it

stephaneeybert · **Joined:** Mon Apr 16, 2007 8:10 am **Posts:** 246

Not solved yet..

If anyone else is enlightened by my log output here..

Quote:

2011-03-23 23:06:59,509 DEBUG [StatisticsVisit] ========================>>> Getting: 2010-05-23T23:06:48.425+02:00
Hibernate:
insert
into
statistics_visit
(version, visit_datetime, visitor_host_address, visitor_browser, visitor_referer, id)
values
(?, ?, ?, ?, ?, null)
2011-03-23 23:06:59,536 TRACE [BasicBinder] binding parameter [1] as [INTEGER] - 0
2011-03-23 23:06:59,545 TRACE [BasicBinder] binding parameter [2] as [TIMESTAMP] - 2010-05-23 21:06:48.425
2011-03-23 23:06:59,546 TRACE [BasicBinder] binding parameter [3] as [VARCHAR] - 112.54.34.111
2011-03-23 23:06:59,546 TRACE [BasicBinder] binding parameter [4] as [VARCHAR] - Safari
2011-03-23 23:06:59,546 TRACE [BasicBinder] binding parameter [5] as [VARCHAR] - http://www.lasuede.com
Hibernate:
call identity()
2011-03-23 23:06:59,566 DEBUG [HibernateTransactionManager] Found thread-bound Session [org.hibernate.impl.SessionImpl@98f352] for Hibernate transaction
2011-03-23 23:06:59,567 DEBUG [HibernateTransactionManager] Participating in existing transaction
2011-03-23 23:06:59,567 DEBUG [StatisticsVisit] ========================>>> Getting: 2009-03-23T23:06:48.425+01:00
Hibernate:
insert
into
statistics_visit
(version, visit_datetime, visitor_host_address, visitor_browser, visitor_referer, id)
values
(?, ?, ?, ?, ?, null)
2011-03-23 23:06:59,570 TRACE [BasicBinder] binding parameter [1] as [INTEGER] - 0
2011-03-23 23:06:59,571 TRACE [BasicBinder] binding parameter [2] as [TIMESTAMP] - 2009-03-23 22:06:48.425
2011-03-23 23:06:59,572 TRACE [BasicBinder] binding parameter [3] as [VARCHAR] - 123.22.454.232
2011-03-23 23:06:59,572 TRACE [BasicBinder] binding parameter [4] as [VARCHAR] - Chrome
2011-03-23 23:06:59,572 TRACE [BasicBinder] binding parameter [5] as [VARCHAR] - http://www.francofil.com
Hibernate:
call identity()
2011-03-23 23:06:59,573 DEBUG [HibernateTransactionManager] Found thread-bound Session [org.hibernate.impl.SessionImpl@98f352] for Hibernate transaction
2011-03-23 23:06:59,573 DEBUG [HibernateTransactionManager] Participating in existing transaction
2011-03-23 23:06:59,574 DEBUG [StatisticsVisit] ========================>>> Getting: 2011-03-23T23:06:48.305+01:00
Hibernate:
insert
into
statistics_visit
(version, visit_datetime, visitor_host_address, visitor_browser, visitor_referer, id)
values
(?, ?, ?, ?, ?, null)
2011-03-23 23:06:59,574 TRACE [BasicBinder] binding parameter [1] as [INTEGER] - 0
2011-03-23 23:06:59,575 TRACE [BasicBinder] binding parameter [2] as [TIMESTAMP] - 2011-03-23 22:06:48.305
2011-03-23 23:06:59,575 TRACE [BasicBinder] binding parameter [3] as [VARCHAR] - 85.231.434.45
2011-03-23 23:06:59,575 TRACE [BasicBinder] binding parameter [4] as [VARCHAR] - Firefox
2011-03-23 23:06:59,576 TRACE [BasicBinder] binding parameter [5] as [VARCHAR] - http://www.google.com
Hibernate:
call identity()
2011-03-23 23:06:59,577 DEBUG [HibernateTransactionManager] Found thread-bound Session [org.hibernate.impl.SessionImpl@98f352] for Hibernate transaction
2011-03-23 23:06:59,577 DEBUG [HibernateTransactionManager] Participating in existing transaction
2011-03-23 23:06:59,577 DEBUG [StatisticsVisit] ========================>>> Getting: 2010-05-23T23:06:48.425+02:00
Hibernate:
insert
into
statistics_visit
(version, visit_datetime, visitor_host_address, visitor_browser, visitor_referer, id)
values
(?, ?, ?, ?, ?, null)
2011-03-23 23:06:59,578 TRACE [BasicBinder] binding parameter [1] as [INTEGER] - 0
2011-03-23 23:06:59,578 TRACE [BasicBinder] binding parameter [2] as [TIMESTAMP] - 2010-05-23 21:06:48.425
2011-03-23 23:06:59,578 TRACE [BasicBinder] binding parameter [3] as [VARCHAR] - 123.22.454.232
2011-03-23 23:06:59,578 TRACE [BasicBinder] binding parameter [4] as [VARCHAR] - Chrome
2011-03-23 23:06:59,578 TRACE [BasicBinder] binding parameter [5] as [VARCHAR] - http://www.francofil.com
Hibernate:
call identity()
2011-03-23 23:06:59,579 DEBUG [HibernateTransactionManager] Found thread-bound Session [org.hibernate.impl.SessionImpl@98f352] for Hibernate transaction
2011-03-23 23:06:59,579 DEBUG [HibernateTransactionManager] Participating in existing transaction
2011-03-23 23:06:59,579 DEBUG [StatisticsVisit] ========================>>> Getting: 2010-05-23T23:06:48.425+02:00
Hibernate:
insert
into
statistics_visit
(version, visit_datetime, visitor_host_address, visitor_browser, visitor_referer, id)
values
(?, ?, ?, ?, ?, null)
2011-03-23 23:06:59,580 TRACE [BasicBinder] binding parameter [1] as [INTEGER] - 0
2011-03-23 23:06:59,590 TRACE [BasicBinder] binding parameter [2] as [TIMESTAMP] - 2010-05-23 21:06:48.425
2011-03-23 23:06:59,593 TRACE [BasicBinder] binding parameter [3] as [VARCHAR] - 112.54.34.111
2011-03-23 23:06:59,593 TRACE [BasicBinder] binding parameter [4] as [VARCHAR] - Safari
2011-03-23 23:06:59,598 TRACE [BasicBinder] binding parameter [5] as [VARCHAR] - http://www.lasuede.com
Hibernate:
call identity()
2011-03-23 23:06:59,599 DEBUG [HibernateTransactionManager] Found thread-bound Session [org.hibernate.impl.SessionImpl@98f352] for Hibernate transaction
2011-03-23 23:06:59,601 DEBUG [HibernateTransactionManager] Participating in existing transaction
2011-03-23 23:06:59,843 DEBUG [StatisticsVisit] ========================>>> Getting: 2010-05-23T23:06:48.425+02:00
2011-03-23 23:06:59,844 DEBUG [StatisticsVisit] ========================>>> Getting: 2009-03-23T23:06:48.425+01:00
2011-03-23 23:06:59,844 DEBUG [StatisticsVisit] ========================>>> Getting: 2011-03-23T23:06:48.305+01:00
2011-03-23 23:06:59,844 DEBUG [StatisticsVisit] ========================>>> Getting: 2010-05-23T23:06:48.425+02:00
2011-03-23 23:06:59,845 DEBUG [StatisticsVisit] ========================>>> Getting: 2010-05-23T23:06:48.425+02:00
2011-03-23 23:06:59,846 DEBUG [StatisticsVisit] ========================>>> Getting: 2011-03-23T23:06:48.305+01:00
2011-03-23 23:06:59,846 DEBUG [StatisticsVisit] ========================>>> Getting: 2009-03-23T23:06:48.425+01:00
2011-03-23 23:06:59,846 DEBUG [StatisticsVisit] ========================>>> Getting: 2010-05-23T23:06:48.425+02:00
2011-03-23 23:06:59,847 DEBUG [StatisticsVisit] ========================>>> Getting: 2010-05-23T23:06:48.425+02:00
2011-03-23 23:06:59,847 DEBUG [StatisticsVisit] ========================>>> Getting: 2010-05-23T23:06:48.425+02:00
Hibernate:
select
count(*) as col_0_0_
from
statistics_visit statistics0_
where
year(statistics0_.visit_datetime)=?
and hour(statistics0_.visit_datetime)=?
2011-03-23 23:06:59,864 TRACE [BasicBinder] binding parameter [1] as [INTEGER] - 2010
2011-03-23 23:06:59,864 TRACE [BasicBinder] binding parameter [2] as [INTEGER] - 23
2011-03-23 23:06:59,878 TRACE [BasicExtractor] found [0] as column [col_0_0_]

chrispheby · **Joined:** Wed Mar 23, 2011 7:01 pm **Posts:** 2

Hi,

I am the maintainer of Jadira Usertype which you are using.

Usertype assumes your database is running in UTC out of the box. Therefore, the actual time written down will also be in UTC. I would suggest that is the difference between the two times.

A parameter is provided on the usertype to enable you to adjust this behaviour in the case that your database does not use UTC.

This is from the Javadoc on the type you are using:

"The type is stored using UTC timezone and presented in the JVM using the JVM's default zone. Alternatively provide the 'databaseZone' parameter in the DateTimeZone.forID(String) format to indicate the zone of the database. The 'javaZone' can be used to similarly configure the zone of the value on return from the database. N.B. To use the zone of the JVM supply 'jvm' "

See http://usertype.sourceforge.net/apidocs ... eTime.html

Let me know if you have further questions or if this addresses your issue.

Thanks Chris

stephaneeybert · **Joined:** Mon Apr 16, 2007 8:10 am **Posts:** 246

Thanks for that comment.

I understand the following:

- The timestamps should be stored with the UTC timezone in the database.

- The database should be set up to be in the UTC timezone and the JVM should be set up to be in the UTC timezone.

- Avoid the need for the configuration option -Duser.timezone="UTC" when running the JVM as it can be forgotten too easily.

From this shaky ground I have a few questions on how to go forward in a Spring Hibernate web application.

Is there any way in the Spring Hibernate configuration to specify this UTC timezone to both the database and the JVM ?

I'm thinking of some bean properties in the same fashion as:

Code:

      <property name="hibernateProperties">
         <props>
            <prop key="hibernate.dialect">${hibernate.dialect}</prop>
            <prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto}</prop>
            <prop key="hibernate.connection.pool_size">0</prop>
            <prop key="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</prop>
            <prop key="hibernate.show_sql">true</prop>
            <prop key="hibernate.format_sql">true</prop>
         </props>
      </property>

I have googled for a while now I can't find anything like a how-to for a standard web application.

sanne.grinovero · **Posted:** Thu Mar 24, 2011 8:31 am

In my opinion it's very hard to have all time-related issues in control, so the best solution is to make sure all application servers, JVMs and databases use UTC. What you "show" to the user is likely going through a presentation framework, with options for the user to specify his preferred timezone. that's where components like JSF get useful, and why Joda is awesome. You would have all the same issues with any other technology (even other platforms), just Joda makes it better to handle.

Quote:

Avoid the need for the configuration option -Duser.timezone="UTC" when running the JVM as it can be forgotten too easily.

"forgot too easily"? don't really agree with that, sysadmins should be well aware of this, and also properly timesync different servers or you get crazy hard to debug issues, especially if backup farms / some appservers are in other regions.

stephaneeybert · **Joined:** Mon Apr 16, 2007 8:10 am **Posts:** 246

I totally agree with you when you say:

"make sure all application servers, JVMs and databases use UTC. What you "show" to the user is likely going through a presentation framework".

It is exactly what I was trying to say in my last post.

That is what should be done.

I need to know about the "how".

How can I do that ?

Is there any Spring / Hibernate way to specify the timezone as UTC ?

Or does this timezone handling needs to be done every time one instantiates a new DateTime object ?

I'm looking for "best practice" and "how-to" here..

Thanks.

chrispheby · **Joined:** Wed Mar 23, 2011 7:01 pm **Posts:** 2

Hi Stephane,

I think you have the following options:

1) Set System Zone to UTC in the operating system

AND/OR

2) Use the system property to set the JVM zone when starting the JVM

AND/OR

3) Use the parameter to Jadira's usertype to set the jvm form (the database form defaults to UTC) together with explicitly setting the zone when constructing new DateTimes

e.g. new DateTime(DateTimeZone.UTC)

I recommend you make the code change to do this anyway. If you are constructing a DateTime you must know the zone you want. I also recommend you do this when formatting the DateTime for screen display.

Thanks Chris

stephaneeybert · **Joined:** Mon Apr 16, 2007 8:10 am **Posts:** 246

So I added a

DateTimeZone.UTC

to all my

new DateTime()

so as to have them all as

new DateTime(DateTimeZone.UTC)

and the unit test now passes fine.

I did not have to fix some setup anywhere in the application.

Thanks for your kind support !