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

assylias · Accepted Answer · 2014-08-22T08:24:07.090

2

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; }
}

edited Aug 22 '14 at 08:24

answered Aug 21 '14 at 18:55

assylias

321,522
82
660
783

*"The JDBC driver caches the timezone"*: Hmm, what JDBC driver does that? Note, there is an ongoing discussion about `TIMESTAMP WITH TIMEZONE` data types on the [jOOQ User Group](https://groups.google.com/forum/#!topic/jooq-user/NzphqHabofE). – Lukas Eder Aug 22 '14 at 06:32
I could not get a similar converter with timestamptz work - maybe related? (it is postgres latest driver) – assylias Aug 22 '14 at 06:59
1

Yes, that's definitely related. [The relevant issue is probably this one here](https://github.com/jOOQ/jOOQ/issues/2738) – Lukas Eder Aug 22 '14 at 07:01
Are you sure that the JDBC driver really caches the timezone? What I would expect is that the driver uses the current default timezone when it establishes the connection and sets that for the connection. This timezone setting stays valid until the connection client changes the timezone using SET TIME ZONE and such. Just changing the default timezone of your JRE wouldn't influence the established connection and session to the DB in that case, but the time zone is not cached at all. – Thorsten Schöning Aug 22 '14 at 09:25
It's simply that during establashing a session the driver communicates the current timezone and afterwards it's up to the connection client to change the timezone as needed. Else the driver would just communicate "things" and send commands using your session in an unpredictable way. I guess your test would suceed if you changed the sessions timezone using the commands of your database client as well. – Thorsten Schöning Aug 22 '14 at 09:28
@ThorstenSchöning That's what I meant, the JVM timezone is retrieved when the connection is established and if the JVM timezone is changed after that it does not affect that connection - but a new connection would take the change into account. Thanks for the clarification. – assylias Aug 22 '14 at 10:05
@LukasEder FYI the problem with the converter of timestamptz was because I did not escape the spaces in `timestamp\ with\ time\ zone`: http://stackoverflow.com/q/33173842/829571. Is this on purpose? (In plain Java, `"timezone with time zone".matches("timezone with time zone")` is true whether the spaces are escaped or not) – assylias Oct 20 '15 at 18:24
@assylias: I've noticed that other issue and I'll comment on there. That's not nice - we've overlooked this effect when turning on the `COMMENTS` flag on Java's regexes... – Lukas Eder Oct 21 '15 at 07:59

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

1 Answers1