Hello. I am trying to get Hibernate to persist the time zone information in a java Calendar type. I've generated some test code that demonstrates my strategy and reveals the problem. I'm using postgresql version 7.4.2.

First, I have a simple class called TimeWithTimeZone that is mapped to a table called time_w_timezone and has a member called testTime that is of type java.util.Calendar. This is the xml generated by xdoclet for the class:

<hibernate-mapping>
<class name="TimeWithTimeZone" table="time_with_timezone"
dynamic-update="false" dynamic-insert="false">

<id name="id" column="id" type="long" unsaved-value="-1">
<generator class="native"></generator>
</id>

<property name="testTime" type="java.util.Calendar" update="true" insert="true">
<column name="test_time" not-null="true"
sql-type="timestamp with time zone"
/>
</property>
</class>
</hibernate-mapping>

Note that I delcare the sql type of testTime to be timestamp with timezone.

I run the schema export task to create the DB and get what I expect. Next, I use the following test code to load and retrieve a time (Note that my local system time zone is Pacific):

public static void main(String[] args) {

try {
Configuration hibernateConfig =
new Configuration().addClass(TimeWithTimeZone.class);
SessionFactory factory = hibernateConfig.buildSessionFactory();
Session session = factory.openSession();

GregorianCalendar c1 =
new GregorianCalendar(TimeZone.getTimeZone("America/New_York"));
TimeWithTimeZone t1 = new TimeWithTimeZone(c1);
Transaction tx = session.beginTransaction();
session.save(t1);
tx.commit();
//session.close();
//session = factory.openSession();
List times = session.find("from TimeWithTimeZone as twtz");
TimeWithTimeZone currentTime;
for (Iterator i = times.iterator(); i.hasNext(); ) {
currentTime = (TimeWithTimeZone)i.next();
System.out.println("TIME ZONE ID: " +
currentTime.getTestTime().getTimeZone().getID())
}
session.close();
} catch (Exception e) {
throw new RuntimeException(e);
}
}

Note the commented out calls to session.close() and factory.openSession in the middle. of the code. As expected, the output of this code is the following:

TIME ZONE ID: America/New_York

Now if I uncomment the session.close and factory.openSession calls, clear the database, and then run the code again, I get the following output, which is my local time zone, not the New York time zone that I set earlier:

TIME ZONE ID: America/Tijuana

It seems as though if I don't close the session, the object is cached somehow and I get the data that I expect. However, if I exit the session and then fetch the data from the database, it is retrieved in my local system timezone, not the time zone that I intended to persist. Is there a native Hibernate type that will automatically initialize to the desired time zone? Am I stuck doing it manually or defining a CompositeUserType?

Thanks,
Brian

Bumping this up. I'm having the same problem. Anyone know if there's a Hibernate type that store a timestamp with a time zone?

Use a custom UserType.

CalendarType.set currently does this:

st.setTimestamp( index, new Timestamp( ( (Calendar) value ).getTime().getTime() ) );

Shouldn't it use the form that also takes a Calendar parameter:

st.setTimestamp( index, new Timestamp( ( (Calendar) value ).getTime().getTime() ), (Calendar) value );

According to the JDBC docs: "With a Calendar object, the driver can calculate the timestamp taking into account a custom timezone"

Hm, that might probably be a good idea - I'd say submit it to JIRA.

Done. HB-1006.

About the HB-1006 - Calendar loses time zone information fix :

Oracle, MySQL, SQLServer and many other databases don't have any time zone information associated to their timestamp column types. Worst than that, those three databases don't serialize the timestamp in a timezone-neutral format ( a long representing milliseconds since a given point in time. For example : 1100700270875 ), but as a "String" representation of it in a given time zone ( For example : 2004-11-17 10:00:00 ).

Since a java.sql.Timestamp only represents a time in millis without any time zone information, and the database takes a String representation in a specific time zone, JDBC provides two methods in the PreparedStatement interface to set timestamp value. The first one only takes the java.sql.Timestamp, and will use the default JVM time zone. The second one takes a java.sql.Timestamp AND a java.util.Calendar, that the statement will use to fetch the time zone. So, for example, if your JVM runs in GMT and you want to set a Calendar that is set to 2004-11-17 10:00:00 EST, if you use the first method, '2004-11-17 15:00:00' will be set, and '2004-11-17 10:00:00' will be set for the second method.

To match those two methods, equivalent methods were published in the ResultSet : getTimestamp( java.sql.Timestamp ) and getTimestamp( java.sql.Timestamp, java.util.Calendar ).

Fix #HB-1006 proposed to use the second method of the PreparedStatement interface to serialize timestamps to the database. Although this fix works for databases that supports "timestamp with time zone" column types, where values put in the database will be '2004-11-17 10:00:00 EST' and '2004-11-17 15:00:00 GMT', for most of the databases, the timestamp saved doesn't contain any time zone information ( '2004-11-17 10:00:00' and '2004-11-17 15:00:00' ), so the value doesn't have any meaning except if we know the time zone used to serialize it, and use the second ResultSet method to get back the value.

Hibernate's CalendarType class, since it needs to be as generic as possible, can't use the second ResultSet method to get the value back from the database. At this point, Hibernate doesn't have any idea in what time zone the value was stored. A user type could be written to serialize and read the values in a hard-coded, different time zone than the default JVM time zone. A user type could also be written to store the timestamp AND the timezone, and this would allow to get the timestamp in the right format. But the default type can't adjust to those features, so i think has to use the first PreparedStatement method, the one that doesn't receive a Calendar and uses the default JVM time zone to serialize the value to the database. I think this fix should be reverted.

What do you think about that ? Is there something I don't understand ?

Thank you !

I don't see any timezone information in both representations.

Sorry, maybe i wasn't clear about that. What i mean is that a time in millis in Java is the difference, measured in milliseconds, between the current time and midnight, January 1, 1970 UTC. So, in my example, 1100700270875 is 2004/11/17 14:04:30 GMT or 2004/11/17 09:04:30 EST. Although a time in millis doesn't have any time zone information, it is not specific either to one time zone.

A String, like '2004/11/17 14:04:30', doesn't have any meaning if no time zone is specified. '2004/11/17 14:04:30 GMT' is NOT the same as '2004/11/17 14:04:30 EST'.

Databases like Oracle, MySQL and SQLServer use by default the String representation, like '2004/11/17 14:04:30'. The time zone in which the values were specified has to be known ( if, for example, you run your server with the default TimeZone as GMT, and use the setTimestamp() method with only one parameter, you know that all the timestamps in the database will be in GMT ).

I hope this clarifies my point !

I store the dates in UTC in the DB, converting to local time with DateFormat where necessary in the user interface.

In order to get hibernate to use the correct timezone I had to set the JVM timezone.
-Duser.timezone="UTC"