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?

Outrigger

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. – Spermophile

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. – Lymphoma

@BasilBourque fixed – Outrigger

@Lymphoma 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. – Outrigger

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"));... – Lymphoma

@Lymphoma 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. – Outrigger

@LarsBohl I think it only works because H2 stores DATE as yyyy-MM-dd, not really something that I'd recommend. However, I found this post #32548831 and the code in the question appears to solve this issue (i.e. if you use the provided getLocalDate). – Lymphoma

Let us continue this discussion in chat. – Outrigger

Answer 1

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

Iceman

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. – Outrigger

Answer 2

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?

Outrigger