1

I am reaching out to the community here because either I found an issue with Hibernate or I just don't understand how to use java.sql.Date and java.time.LocalDate.

I'm running into a problem where my DB is in UTC time zone while my client is in EST. I have a field in the DB called ETA of type DATE that for example is set to 2019-09-10 on a record. When I read the date in EST it becomes 2019-09-09. The DATE field according to documentation has no timezone information.

When Hibernate reads the value, it uses the DateTypeDescriptor.java class. However, the problem with that class is that it will first try to read the value as a java.sql.Date (in the rs.getDate( name ) part) and then call java.sql.Date.toLocalDate() in the javaTypeDescriptor.wrap() part, which is implemented poorly because what it does is it strips off the timezone offset information and just plainly returns a date. This makes 2019-09-09T20:00:00.000-0400 (which is 2019-09-10 in the database) to become 2019-09-09 without the timezone offset part.

What I consider the problem is the part where Hibernate calls rs.getDate() because that must return java.sql.Date. Now, the MySQL driver contains a method public LocalDate getLocalDate(int columnIndex) to obtain a LocalDate, so I don't understand why Hibernate isn't using that method.

I found that someone has already brought up this same problem with them but they don't seem to consider it an issue.

Therefore, I'm reaching out here to understand - is there a bug with Hibernate or am I just not understanding correctly how to convert between DATE (DB) and LocalDate (Java) types.

PS: I use latest Hibernate 5.4.5 and latest JPA 2.2.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Stefan Zhelyazkov
  • 2,599
  • 4
  • 16
  • 41
  • 4
    JDBC does not provide a `getLocalDate` method. The fact MySQL has it is an implementation-specific thing. Hibernate can't use it. Instead it should use `getObject(index/name, LocalDate.class)`. – Mark Rotteveel Sep 23 '19 at 17:33
  • @MarkRotteveel Perhaps you should make an Answer of your Comment, so this question can be marked resolved. – Basil Bourque Sep 24 '19 at 02:31
  • Related: [*JPA support for Java 8 new date and time API*](https://stackoverflow.com/q/23718383/642706) – Basil Bourque Sep 24 '19 at 02:40
  • @BasilBourque I intentionally posted as a comment because I don't think it answers the question. – Mark Rotteveel Sep 24 '19 at 06:55
  • JDBC specifies that getOjbect(int, Class) accepts LocalDate as the second argument. @MarkRotteveel, I think you know that the JDBC EG is no longer adding type specific getXXX and setXXX methods. All new types will use the generic getObject(int, Class) and setObject(int, Object, SQLType) methods. The JDBC spec specifically says that LocalDate, LocalTime, LocalDateTime, ands OffsetDateTime are supported by these methods. No need for any implementation specific methods. IMO adding such methods just confuses people. – Douglas Surber Jul 24 '20 at 22:01
  • @DouglasSurber I know that, and I'm not sure what in my earlier comments implied that I thought otherwise. It's just that in the context of this question it isn't the answer by itself. – Mark Rotteveel Jul 25 '20 at 06:26

1 Answers1

1

Try Hibernate 5 and the configuration option hibernate.jdbc.time_zone. That's an official way to force it to use JDBC APIs which use an Calendar instance in the UTC timezone.

That said, I've found that there are a lot of problems in this area. JDBC drivers and people using databases often get this subtly wrong. That's why we eventually gave up and just put the milliseconds (in UTC) into the database. So the column type is always NUMBER(16) for all three temporal types.

That allows us to write special converters which return predictable results, independent of JDBC drivers, database/VM/OS time zones.

Programmers can then concentrate on trying to understand why timezones don't work they way they think they should :-)

Aaron Digulla
  • 321,842
  • 108
  • 597
  • 820
  • Hi Aaron, I was indeed using Hibernate 5, specifically `5.4.5`, and JPA `2.2`. I am pretty sure I tried setting `hibernate.jdbc.time_zone` and it did not work. I will upvote your answer for your suggestion to use column type `NUMBER(16)`. I have seen this suggested in several other places as a good alternative to using date types. – Stefan Zhelyazkov Jul 25 '20 at 14:18