1

How can I instruct Hibernate to map a Java date to and from an Oracle DATE column using a time zone other than the local time zone?

Sean Patrick Floyd
  • 292,901
  • 67
  • 465
  • 588
Derek Mahar
  • 27,608
  • 43
  • 124
  • 174

2 Answers2

1

I'm not sure but I think that you'll have to use the OracleConnection.setSessionTimeZone(String regionName) method to set the session time zone.

To do that on the OracleConnection before having Hibernate dealing with it, the cleanest way would be to provide and use a custom implementation of o.h.c.ConnectionProvider.

See this related question.

Community
  • 1
  • 1
Pascal Thivent
  • 562,542
  • 136
  • 1,062
  • 1,124
  • Perhaps I'll just normalize the date to the target time zone before handing it over to Hibernate to store in the table. – Derek Mahar Nov 06 '10 at 14:48
  • Pascal, does JDBC translate the JVM time zone to the database session time zone? I just asked this question at http://stackoverflow.com/questions/4123534/before-writing-a-java-date-to-an-sql-timestamp-column-does-jdbc-translate-the-da. – Derek Mahar Nov 08 '10 at 11:53
  • Based on the answers to my other question, it seems that time zone translation is specific to the JDBC driver implementation. – Derek Mahar Nov 08 '10 at 15:29
  • @Derek I confess, I don't know what the JDBC spec says about that. But that's where I'd look first. Then, I'll check the conformance of a given JDBC driver. – Pascal Thivent Nov 08 '10 at 15:37
  • The JDBC 4.0 Specification (http://jcp.org/en/jsr/detail?id=221) doesn't even mention time zones! Consequently, JDBC doesn't support SQL type `TIMESTAMP WITH TIME ZOME` and does not recognize `TIMESTAMP` literals that include a time zone. So, time zone interpretation must be entirely dependent on JDBC driver implementation! – Derek Mahar Nov 08 '10 at 15:55
  • @Derek That clears things out. And somehow, I think it's not a bad thing. Personally, I would *store* everything in UTC and handle time zone for *display* only. Actually, I'm wondering what you have in mind with all these questions about Date, Calendar, JPA, TIMESTAMP, time zone. You're playing with fire :) – Pascal Thivent Nov 08 '10 at 16:01
  • Pascal, I agree that I should store all dates in UTC, but what is the best way to do this given certain constraints? Must I change the JVM time zone, the database session time zone, or the database time zone? Must they all be set to the same time zone? What if I have control over only the JVM time zone? Should I explicitly specify UTC with every date that I send to the database? What if the database already contains dates in a time zone other than UTC, say UTC-5? Should I stick with UTC-5 or translate the existing dates to UTC? – Derek Mahar Nov 08 '10 at 17:16
  • Pascal, in general, I'm trying to get a more complete understanding of how Hibernate and JDBC treat dates with respect to a time zone and how this treatment differs from that of an SQL query tool. Apparently, Hibernate mostly delegates the matter to JDBC and how JDBC treats the time zone is implementation specific, so an application should not assume too much about how its JDBC driver may translate dates with respect to its time zone and so should be as explicit as possible about the time zone. – Derek Mahar Nov 08 '10 at 17:46
  • @Derek I'll give some feedback, a bit later. – Pascal Thivent Nov 08 '10 at 17:52
0

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 this blog post: http://dev-metal.blogspot.com/2010/11/mapping-dates-and-time-zones-with.html

joobik
  • 57
  • 1
  • Joobik, in your article, you claim that Hibernate translates the time zone of the given Date: "So what Hibernate does, is using the time zone schedule of its JVM (which runs in CET) to convert the time zone independent java.util.Date to the time zone specific date string in our DB record. Because CET is one hour ahead of GMT, one hour has been added to our DB date." However, according to http://stackoverflow.com/questions/4123534/before-writing-a-java-date-to-an-sql-timestamp-column-does-jdbc-translate-the-da/4124620#4124620, it's the JDBC driver that translates the time zone, not Hibernate. – Derek Mahar Dec 09 '10 at 21:02