Missed opportunity to fix JDBC date handling in Java 8?

Question

Can any Java 8 + JDBC expert tell me if something's wrong in the following reasoning? And, if in the secrets of Gods, why this hasn't been done?

A java.sql.Date is currently the type used by JDBC to map to the DATE SQL type, which represents a date without time, and without timezone. But this class is awfully designed, since it's in fact a subclass of java.util.Date, which stores a precise instant in time, up to the millisecond.

To represent the date 2015-09-13 in database, we're thus forced to choose a timezone, parse the string "2015-09-13T00:00:00.000" in that timezone as a java.util.Date to get a millisecond value, then construct a java.sql.Date from this millisecond value, and finally call setDate() on the prepared statement, passing a Calendar holding the timezone chosen in order for the JDBC driver to be able to correctly recompute the date 2015-09-13 from this millisecond value. This process is made a bit simpler by using the default timezone everywhere, and not passing a Calendar.

Java 8 introduces a LocalDate class, which is a much better fit for the DATE database type, since it's not a precise moment in time, and is thus not dependent on the timezone. And Java 8 also introduces default methods, which would allow to make backward-compatible changes to the PreparedStatement and ResultSet interfaces.

So, haven't we missed a huge opportunity to clean up the mess in JDBC while still maintaining backward compatibility? Java 8 could simply have added those default methods to PreparedStatement and ResultSet:

default public void setLocalDate(int parameterIndex, LocalDate localDate) {
    if (localDate == null) {
        setDate(parameterIndex, null);
    }
    else {
        ZoneId utc = ZoneId.of("UTC");
        java.util.Date utilDate = java.util.Date.from(localDate.atStartOfDay(utc).toInstant());
        Date sqlDate = new Date(utilDate.getTime());
        setDate(parameterIndex, sqlDate, Calendar.getInstance(TimeZone.getTimeZone(utc)));
    }
}

default LocalDate getLocalDate(int parameterIndex) {
    ZoneId utc = ZoneId.of("UTC");
    Date sqlDate = getDate(parameterIndex, Calendar.getInstance(TimeZone.getTimeZone(utc)));
    if (sqlDate == null) {
        return null;
    }

    java.util.Date utilDate = new java.util.Date(sqlDate.getTime());
    return utilDate.toInstant().atZone(utc).toLocalDate();
}

Of course, the same reasoning applies to the support of Instant for the TIMESTAMP type, and the support of LocalTime for the TIME type.

Instead of doing that, they are simply telling you to use the `setObject` method. If the object happens to be a `LocalDate`, up-to-date JDBC drivers are supposed to know what to do with it. — RealSkeptic, Sep 13 '15 at 09:50
I've seen that. But it's a pity, because we have to wait for up-to-date drivers, and libraries can never be sure the JDBC driver will indeed support LocalDate, so they reimplement this functionality instead of relying on a default implementation. And we still have to do getDate().toLocalDate() on a ResultSet to get a LocalDate. — JB Nizet, Sep 13 '15 at 10:02
Or use a `getObject(int,Class)` and rely on the JDBC driver to be able to handle it. Anyway, that's their policy for now. Question is, what kind of answers do you expect to get from SO on this issue? — RealSkeptic, Sep 13 '15 at 10:06
The one I got is OK, and I'll accept it. Basically, I read it as "yes, I agree, but there is a workaround". I would also have accepted "your reasoning is completely flawed, and here's why" :-) — JB Nizet, Sep 13 '15 at 10:18
@JBNizet this is a big can of worms, we have decided on one big project to represent date without time and without timezone as a String instead... — vikingsteve, Sep 13 '15 at 10:18
Here is some insight into the decision making: [Overview on OpenJdK mailing list](http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2012-December/000010.html). — RealSkeptic, Sep 13 '15 at 10:28
That's ridiculous but it's the 10th of January, 2017 now and Oracle still don't provide JDBC 4.2 driver, what a shame! Microsoft presented it's JDBC 4.2-compatible driver in the September of 2015! — Roman Sinyakov, Jan 10 '17 at 09:52

score 39 · Accepted Answer · edited Sep 14 '15 at 01:14

39

To represent the date 2015-09-13 in database, we're thus forced to choose a timezone, parse the string "2015-09-13T00:00:00.000" in that timezone as a java.util.Date to get a millisecond value, then construct a java.sql.Date from this millisecond value, and finally call setDate() on the prepared statement, passing a Calendar holding the timezone chosen in order for the JDBC driver to be able to correctly recompute the date 2015-09-13 from this millisecond value

Why? Just call

Date.valueOf("2015-09-13"); // From String
Date.valueOf(localDate);    // From java.time.LocalDate

The behaviour will be the correct one in all JDBC drivers: The local date without timezone. The inverse operation is:

date.toString();    // To String
date.toLocalDate(); // To java.time.LocalDate

You should never rely on java.sql.Date's dependency on java.util.Date, and the fact that it thus inherits the semantics of a java.time.Instant via Date(long) or Date.getTime()

So, haven't we missed a huge opportunity to clean up the mess in JDBC while still maintaining backward compatibility? [...]

It depends. The JDBC 4.2 spec specifies that you are able to bind a LocalDate type via setObject(int, localDate), and to fetch a LocalDate type via getObject(int, LocalDate.class), if the driver is ready for that. Not as elegant as more formal default methods, as you suggested, of course.

edited Sep 14 '15 at 01:14

Basil Bourque

303,325
100
852
1,154

answered Sep 13 '15 at 10:04

Lukas Eder

211,314
129
689
1,509

3

I missed the valueOf(LocalDate) method (I was looking for a from() method like `java.util.Date.from(Instant)`), and forgot about the valueOf(String) method. It's still is mess, since we have to know which timezone was used when constructing the Date to call the correct setDate() method. I'm not sure how I can avoid relying on the fact that it uses milliseconds, since the only non-deprecated constructor it has takes milliseconds as argument. – JB Nizet Sep 13 '15 at 10:17
1

Regarding the last part, I find it ugly: we have to wait for Java8-compliant drivers, and it's far from obvious that setObject()/getObject() must be used instead of the usual setXxx()/getXxx() methods existing for all the other types. LocalDate isn't even mentioned in the javadoc. – JB Nizet Sep 13 '15 at 10:17
5

It is an ugly mess, yes. With `java.sql.Date`, things get a bit simpler when you just look at it as a container of the string version of the date. However, try not to send a serialized instance of `java.sql.Date` to a client on another timezone. – Lukas Eder Sep 13 '15 at 10:24
2

"The behaviour will be the correct one in all JDBC drivers: The local date without timezone." I didn't get that part. Because `java.sql.Date` does track timezone inherently, _some_ time zone has to be assumed. Does `Date.valueOf(localDate)` and `Date.toLocalDate()` automatically convert to/from UTC? Unfortunately from a bit of research I suspect it uses the default (system) time zone, which would mean we still have to do time zone gymnastics to normalize to/from UTC, don't we? – Garret Wilson Nov 03 '17 at 23:37
@GarretWilson *"Because java.sql.Date does track timezone inherently"* - If used properly, it doesn't do that. But yes, it's easy to mis-use this type due to its extending `java.util.Date`, e.g. by calling its `Date(long)` constructor, which makes no sense at all, and which unfortunately isn't deprecated. / *"Does Date.valueOf(localDate) and Date.toLocalDate() automatically convert to/from UTC? "* Why should UTC play a role? The type is a local date. It has no notion of time zones. – Lukas Eder Nov 04 '17 at 22:56
Because `java.sql.Date` extends `java.util.Date`, I assumed it keeps track of the "date" by using a certain number of milliseconds since the epoch in UTC, with the time zeroed out. That's how `java.util.Date` works, which means it must be how `java.sql.Date`, which extends it, works. So to go round-trip `LocalDate`->`java.sql.Date`->SQL DATE->`java.sql.Date`->`LocalDate`, you have to use the same time zone conversion to/from `Date` in order to have the number of millisecond wind up the same, right? Does `Date.valueOf(localDate)` guarantee it uses UTC to determine milliseconds for `Date`? – Garret Wilson Nov 04 '17 at 23:25
2

...and in fact, if you follow the source code, you'll see that `java.sql.Date.valueOf(LocalDate date)` extracts the year, month, and day from the `LocalDate` (good) but then eventually calls the `java.util.Date(int year, int month, int date, int hrs, int min, int sec)` constructor, which assumes the values are "in the local time zone" :( So... it seems to me that `java.sql.Date` is indeed inherently tracking the time zone, the conversion from `LocalDate` will be in the local time zone, and if you turn around and load it from the DB on another system you could get a different `LocalDate`. – Garret Wilson Nov 04 '17 at 23:28
1

I guess one other question is whether JDBC guarantees that extracting the SQL DATE from a `java.sql.Date` will also use the local time zone; if JDBC guarantees this, and you don't change the time zone in the middle, you may get lucky and `LocalDate`->`java.sql.Date`->SQL Date will store the correct `YYYY-MM-DD`. But if you were to switch time system time zone after converting to `java.sql.Date` before storing in the database (and that actually could happen) you'd still be be screwed. It seems brittle without normalizing to UTC. – Garret Wilson Nov 04 '17 at 23:36
@GarretWilson: Well, the Lishkov substitution principle was violated many times in early JDK libraries... The JDBC spec clearly indicates what a `java.sql.Date` is. It just doesn't match the expectation given the inheritance. – Lukas Eder Nov 06 '17 at 08:53
1

I think the Lishkov Substitution Principle is missing the point. Whatever the JDBC spec indicates that `java.sql.Date` _means_, its _internal implementation_ has certain real-life implications. If you don't normalize to UTC, there are two scenarios in which you could read different values than what you wrote. Do you understand what I'm saying? I suppose I'll need to create a unit tests to show this more clearly. Maybe I'm completely off track here, but I'm not sure everyone is understanding my point. – Garret Wilson Nov 06 '17 at 13:05
@GarretWilson: Please ask a new question. – Lukas Eder Nov 06 '17 at 16:17

Philippe Marschall · Answer 2 · 2017-05-20T23:20:59.787

Short answer: no, date time handling is fixed in Java 8 / JDBC 4.2 because it has support for Java 8 Date and Time types. This can not be emulated using default methods.

Java 8 could simply have added those default methods to PreparedStatement and ResultSet:

This is not possible for several reasons:

java.time.OffsetDateTime has not equivalent in java.sql
java.time.LocalDateTime breaks on DST transitions when converting through java.sql.Timestamp because the latter depends on the JVM time zone, see code below
java.sql.Time has millisecond resolution but java.time.LocalTime has nanosecond resolution

Therefore you need proper driver support, default methods would have to convert through java.sql types which introduces data loss.

If you run this code in a JVM time zone with daylight savings time it will break. It searches the next transition in which the clocks are "set forward" and picks a LocalDateTime that is right in the middle of the transition. This is perfectly valid because Java LocalDateTime or SQL TIMESTAMP have no time zone and therefore no time zone rules and therefore no daylight saving time. java.sql.Timestamp on the other hand is bound to the JVM time zone and therefore subject to daylight saving time.

ZoneId systemTimezone = ZoneId.systemDefault();
Instant now = Instant.now();

ZoneRules rules = systemTimezone.getRules();
ZoneOffsetTransition transition = rules.nextTransition(now);
assertNotNull(transition);
if (!transition.getDateTimeBefore().isBefore(transition.getDateTimeAfter())) {
  transition = rules.nextTransition(transition.getInstant().plusSeconds(1L));
  assertNotNull(transition);
}

Duration gap = Duration.between(transition.getDateTimeBefore(), transition.getDateTimeAfter());
LocalDateTime betweenTransitions = transition.getDateTimeBefore().plus(gap.dividedBy(2L));

Timestamp timestamp = java.sql.Timestamp.valueOf(betweenTransitions);
assertEquals(betweenTransitions, timestamp.toLocalDateTime());

Missed opportunity to fix JDBC date handling in Java 8?

2 Answers2

Linked