How to map sql DATE to LocalDate

Question

I want to store a LocalDate in a DATE column and retrieve it unchanged. Both DATE and LocalDate are "local" types by definition. Therefore, the concept of timezone should not interfere in any way.

The code below is a minimal example that creates a table with a DATE column in a in-memory database. The maven artifact com.h2database:h2:1.4.192 must be in the classpath.

First, define methods insert and retrieve:

static void insert(DataSource ds, String date) throws SQLException {
  try (Connection conn = ds.getConnection();
       Statement stmt = conn.createStatement()) {
    stmt.execute("CREATE TABLE people (id BIGINT NOT NULL AUTO_INCREMENT"
      + ", born DATE NOT NULL, PRIMARY KEY (id) );");
    stmt.execute("INSERT INTO people (born) VALUES ('" + date + "')");
  }
}

static LocalDate retrieve(DataSource ds) throws SQLException {
  try (Connection conn = ds.getConnection();
       Statement stmt = conn.createStatement();
       ResultSet rs = stmt.executeQuery("SELECT * FROM people limit 1")) {
    if (rs.next()) {
      java.sql.Date retrieved = java.sql.Date.valueOf(rs.getString("born"));
      return retrieved.toLocalDate();
    }
    throw new IllegalStateException("No data");
  }
}

Notice that the insert method uses the toString value of the LocalDate in single quotes, so there's no opportunity for Java™ to create timezone ambiguity. Now call insert once and then several times retrieve, with different timzone settings each time:

public static void main(String[] args) throws Exception {
  DataSource ds = JdbcConnectionPool.create("jdbc:h2:mem:test", "sa", "sa");
  LocalDate born = LocalDate.parse("2015-05-20");
  insert(ds, born.toString());
  System.out.println("Inserted:  " + born);
  for (int i : new int[]{-14, 0, 12}) {
    TimeZone z = TimeZone.getTimeZone(String.format("Etc/GMT%+02d", i));
    TimeZone.setDefault(z);
    System.out.println("Retrieved: " + retrieve(ds));
  }
}

Then the following is printed:

Inserted:  2015-05-20
Retrieved: 2015-05-20
Retrieved: 2015-05-19
Retrieved: 2015-05-18

How to write the retrieve method so that it returns the same value that was inserted unconditionally, assuming that the database table doesn't change?

Are you importing java.util.Date? On that `retrieved =` line you use the fully-qualified `java.sql.Date` on the left side of assignment but not the right. — Basil Bourque, Jun 12 '16 at 22:48
Why were you expecting the 3 same `LocalDate` to be printed? You're inserting the date `2015-05-20`. Then, when you change the default time-zone, the `java.sql.Date.toLocalDate` method will return different result. — Tunaki, Jun 12 '16 at 22:48
@Tunaki I'm not expecting this code to work, I know it doesn't. I'm asking how to write it, so that it does. Both LocalDate and DATE are suitable for birthdays, i.e. Timezone-agnostic. System timezone should not be taken into consideration, this only happens because java forces the conversion to `java.sql.Date`. — Lars Bohl, Jun 12 '16 at 22:54
To not be tied implicitely to the system timezone, I expect (or would like) to be in the wrong here, but I'd just use a `String` with `return LocalDate.parse(rs.getString("born"));`... — Tunaki, Jun 12 '16 at 23:32
@Tunaki Correct, `rs.getString("born")` can still be used, even if `born` is a `DATE` and not `varchar`. Please make a full anwer, so that I can accept. — Lars Bohl, Jun 13 '16 at 05:09
@LarsBohl I think it only works because H2 stores DATE [as yyyy-MM-dd](http://www.h2database.com/html/datatypes.html#date_type), not really something that I'd recommend. However, I found this post http://stackoverflow.com/questions/32548331/missed-opportunity-to-fix-jdbc-date-handling-in-java-8 and the code in the question appears to solve this issue (i.e. if you use the provided `getLocalDate`). — Tunaki, Jun 13 '16 at 08:14
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/114503/discussion-between-lars-bohl-and-tunaki). — Lars Bohl, Jun 13 '16 at 08:30

Gord Thompson · Accepted Answer · 2016-06-15T17:25:05.037

5

I just tried the following modification to your retrieve method and it worked for me:

The H2 documentation for the DATE Type says that it is

The date data type. The format is yyyy-MM-dd.

So, instead of your ...

java.sql.Date retrieved = (java.sql.Date) rs.getObject("born");
return retrieved.toLocalDate();

... I just used ...

return LocalDate.parse(rs.getString("born"));

... and my code produced

Inserted:  2015-05-20
Retrieved: 2015-05-20
Retrieved: 2015-05-20
Retrieved: 2015-05-20

edited Jun 15 '16 at 17:25

answered Jun 13 '16 at 15:43

Gord Thompson

116,920
32
215
418

Looks safe to me, except maybe null handling. Thanks! I'm still wondering how to do it with Oracle DB instead of H2 (which stores a second's precision in a `DATE`). That would be a different question, though. – Lars Bohl Jun 13 '16 at 16:17

Lars Bohl · Answer 2 · 2016-06-18T20:16:26.307

The following solution also works. I prefer the conversion via String in the accepted answer, because it avoids the timezone tinkering shown below. It may however not work the same way on all databases because some, e.g. Oracle, have a different definition of DATE.

static LocalDate retrieve(DataSource ds) throws SQLException {
  try (Connection conn = ds.getConnection();
       Statement stmt = conn.createStatement();
       ResultSet rs = stmt.executeQuery("SELECT * FROM people limit 1")) {
    if (rs.next()) {
      ZoneId utc = ZoneId.of("UTC");
      TimeZone z = TimeZone.getTimeZone(utc);
      Calendar cal = Calendar.getInstance(z);
      java.sql.Date retrieved = rs.getDate("born", cal);
      long time = retrieved.getTime();
      java.util.Date utilDate = new java.util.Date(time);
      Instant instant = utilDate.toInstant();
      ZonedDateTime zdt = instant.atZone(utc);
      return zdt.toLocalDate();
    }
  }
  throw new IllegalStateException("No data");
}

The conversion via java.util.Date is outlined in this question, as suggested by user Tunaki: Missed opportunity to fix JDBC date handling in Java 8?

How to map sql DATE to LocalDate

2 Answers2

Linked