ResultSet#getDate() semantics

Question

We migrated from to ojdbc6-11.2.0.3.0 to ojdbc7-12.1.0.1 and observed a change in the ResultSet#getDate() semantics. Previously the java.sql.Date returned would be 'normalized' by having set the hours, minutes, seconds and milliseconds to zero according to the contract specified on java.sql.Date. With ojdbc7this is no longer the case and java.sql.Date has the hours, minutes, seconds and milliseconds set according to the value on the database.

I looked at the Javadoc of ResultSet#getDate() and it doesn't explicitly say which of the behaviors is the correct one. I would have assumed the old behavior was what the specification intended. Am I right? Have we encountered a driver bug?

Answer 1

This is clearly a bug as the documentation of the class java.sql.Date states

To conform with the definition of SQL DATE, the millisecond values wrapped by a java.sql.Date instance must be 'normalized' by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated.

Answer 2

Oracle is not changing the JDBC specification. We have updated the Oracle Database JDBC driver docs. If there is anything in the docs which is confusing or incorrect we will fix that. No change to the JDBC spec, though.

The previous versions of the drivers are inconsistent. In some places they zero'd the seconds. In other places they did not. In 12.1 we made the drivers consistent. The question was, what was the right behavior. Either way some customers would see a change in behavior.

After a long and vigorous debate we decided that the best thing for Oracle Database customers was not to zero the seconds. Let me explain.

• The ANSI SQL DATE type does not store seconds. The JDBC spec mostly assumes ANSI SQL.
• The Oracle Database DATE type does store seconds. I have been assured by members of the ANSI SQL committee that this is fully compliant with ANSI SQL.
• The goal of JDBC is to expose the database. The JDBC spec does not define some abstract database with the intent that the drivers implement that abstract database. JDBC drivers are required to expose the details of the database, not hide them. JDBC defines tools to abstract some database details but the programmer has the choice to use or not use those tools.
• java.sql.Date does not enforce the zero seconds behavior though it easily could. The onus is on the program to enforce or not enforce that behavior.

So, Oracle DATE has seconds. The Oracle JDBC driver exposes the Oracle Database. If getDate zero'd the seconds, that would lose data. For some users this wouldn't matter, but for others it would. Since Oracle DATE stores seconds many Oracle Databases store times with second precision in DATE columns. Zeroing the seconds in those cases would lose information.

If the program passes a Date with non-zero seconds to setDate, the program has created a non-compliant Date. If the driver zeros the seconds, the driver has thrown away information that the program provided and that the database can store. Again the driver has lost information.

It's easy enough to write SQL or Java to zero the seconds on both get and set. It is more difficult to work around losing information, though certainly possible.

So we chose to make the drivers consistently keep the seconds on java.sql.Dates. ResultSet.getDate can construct a java.sql.Date with non-zero seconds, however this accurately reflects what is in the database. As previously noted the implementation of Date could have enforced this but did not. One way to look at it is that the program created the Date when it stored it in the database so it is the program's responsibility. The driver is just using the data the program provided.

I'm really sorry the drivers were inconsistent before. We are constantly striving to clean up the inconsistencies and strange corner cases. But we have an enormous installed base. Every time we change something, even obvious bug fixes, some customer somewhere is impacted. So we try to strike a balance between improving the drivers and maintaining backward compatibility. 12c is a major release. We took that opportunity to make some more visible changes. We regret the disruption but think it is the right thing to do for the customers overall.