jooq Converter: from java.sql.Date to java.time.LocalDate

Question

I have tried to write a Converter<java.sql.Date, java.time.LocalDate> but I can't get it to work with all time zone settings.

The idea:

• if the client code has a LocalDate, say 20-Aug-2014, and saves it to the DB, it should appear as 20-Aug-2014 in the DB, no matter what the client time zone is.
• if the DB contains a date of 20-Aug-2014, the client should receive a LocalDate of 20-Aug-2014, no matter what the client time zone is.

My test:

@Test public void dateConverter() {
  for (int offset = -12; offset <= 12; offset++) {
    TimeZone localTz = TimeZone.getTimeZone(ZoneOffset.ofHours(offset));
    TimeZone.setDefault(localTz);
    LocalDate ld = LocalDate.now();

    sql.insertInto(DATE_TEST).set(new DateTestRecord(ld)).execute();
    LocalDate savedLd = sql.selectFrom(DATE_TEST).fetchOne(DATE_TEST.DATE_);
    assertEquals(savedLd, ld, "offset=" + offset);
    sql.delete(DATE_TEST).execute();
  }
}

My converter:

public class DateConverter implements Converter<Date, LocalDate>{
  @Override public LocalDate from(Date date) { return date.toLocalDate(); }
  @Override public Date to(LocalDate ld) { return Date.valueOf(ld); }
  @Override public Class<Date> fromType() { return Date.class; }
  @Override public Class<LocalDate> toType() { return LocalDate.class; }
}

I have tried various variations but none worked...

Answer 1

The problem is actually in the test! The JDBC driver caches the timezone when it is created and the time zone updates in the test loop were not taken into account. Taking a new connection every time the timee zone changes in the test makes it pass.

So the code in the question works for a Date to LocalDate converter (except that it should accept null). Final version:

public class DateConverter implements Converter<Date, LocalDate> {
  @Override public LocalDate from(Date date) { return date == null ? null : date.toLocalDate(); }
  @Override public Date to(LocalDate ld) { return ld == null ? null : Date.valueOf(ld); }
  @Override public Class<Date> fromType() { return Date.class; }
  @Override public Class<LocalDate> toType() { return LocalDate.class; }
}

A time with time zone to OffsetTime converter can be done in a similar fashion:

public class TimeConverter implements Converter<Time, OffsetTime> {
  @Override public OffsetTime from(Time time) {
    return time == null ? null : OffsetTime.ofInstant(Instant.ofEpochMilli(time.getTime()), ZoneOffset.systemDefault());
  }
  @Override public Time to(OffsetTime offsetTime) {
    return offsetTime == null ? null : new Time(offsetTime.atDate(LocalDate.ofEpochDay(0)).toInstant().toEpochMilli());
  }
  @Override public Class<Time> fromType() { return Time.class; }
  @Override public Class<OffsetTime> toType() { return OffsetTime.class; }
}