Richard, I rediscovered some of what you have reported here. That was an extraordinary marshalling of info to explain things in detail!
I also found a happy way to solve this in context of using iBATIS and JDBC. I posted my fix back on stackoverflow: using iBATIS custom type handler to address Oracle DATE mapping<http://stackoverflow.com/questions/383783/oracle-sql-date-conversion-problem-using-ibatis-via-java-jdbc#384268> I've essentially reverted DATE mapping behavior back to prior Oracle JDBC driver 9.2 approach. This worked just fine and I'm now a happy camper. --RogerV On Sun, Dec 21, 2008 at 12:14 AM, Richard Yee <[email protected]> wrote: > Roger, > See: > http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.html#08_01 > > Specifically: > Simple Data Types What is going on with DATE and TIMESTAMP? > > This section is on *simple* data types. :-) > > Prior to 9.2, the Oracle JDBC drivers mapped the DATE SQL type to > java.sql.Timestamp. This made a certain amount of sense because the Oracle > DATE SQL type contains both date and time information as does > java.sql.Timestamp. The more obvious mapping to java.sql.Date was somewhat > problematic as java.sql.Date does not include time information. It was > also the case that the RDBMS did not support the TIMESTAMP SQL type, so > there was no problem with mapping DATE to Timestamp. > > In 9.2 TIMESTAMP support was added to the RDBMS. The difference between > DATE and TIMESTAMP is that TIMESTAMP includes nanoseconds and DATE does > not. So, beginning in 9.2, DATE is mapped to Date and TIMESTAMP is mapped > to Timestamp. Unfortunately if you were relying on DATE values to contain > time information, there is a problem. > > There are several ways to address this problem: > > - > > Alter your tables to use TIMESTAMP instead of DATE. This is probably > rarely possible, but it is the best solution when it is. > - > > Alter your application to use defineColumnType to define the columns as > TIMESTAMP rather than DATE. There are problems with this because you > really don't want to use defineColumnType unless you have to (see What > is defineColumnType and when should I use > it?<http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.html#06_01>). > > - > > Alter you application to use getTimestamp rather than getObject. This > is a good solution when possible, however many applications contain generic > code that relies on getObject, so it isn't always possible. > - > > Set the V8Compatible connection property. This tells the JDBC drivers > to use the old mapping rather than the new one. You can set this flag > either > as a connection property or a system property. You set the connection > property by adding it to the java.util.Properties object passed to > DriverManager.getConnection or to > OracleDataSource.setConnectionProperties. You set the system property > by including a -D option in your java command line. > java -Doracle.jdbc.V8Compatible="true" MyApp > > Oracle JDBC 11.1 fixes this problem. Beginning with this release the > driver maps SQL DATE columns to java.sql.Timestamp by default. There is no > need to set V8Compatible to get the correct mapping. V8Compatible is > strongly deprecated. You should not use it at all. If you do set it to true > it won't hurt anything, but you should stop using it. > > Although it was rarely used that way, V8Compatible existed not to fix the > DATE to Date issue but to support compatibility with 8i databases. 8i (and > older) databases did not support the TIMESTAMP type. Setting V8Compatiblenot > only caused SQL DATE to be mapped to > Timestamp when read from the database, it also caused all Timestamps to be > converted to SQL DATE when written to the database. Since 8i is desupported, > the 11.1 JDBC drivers do not support this compatibility mode. For this > reason V8Compatible is desupported. > > As mentioned above, the 11.1 drivers by default convert SQL DATE to > Timestamp when reading from the database. This always was the right thing > to do and the change in 9i was a mistake. The 11.1 drivers have reverted to > the correct behavior. Even if you didn't set V8Compatible in your > application you shouldn't see any difference in behavior in most cases. You > may notice a difference if you use getObject to read a DATE column. The > result will be a Timestamp rather than a Date. Since Timestamp is a > subclass of Date this generally isn't a problem. Where you might notice a > difference is if you relied on the conversion from DATE to Date to > truncate the time component or if you do toString on the value. Otherwise > the change should be transparent. > > If for some reason your app is very sensitive to this change and you simply > must have the 9i-10g behavior, there is a connection property you can set. > Set mapDateToTimestamp to false and the driver will revert to the default > 9i-10g behavior and map DATE to Date. > If possible, you should change your column type to TIMESTAMP instead of > DATE. > > -Richard > > > > Roger Voss wrote: > > I posted following question/problem on stackoverflow, so if anyone knows a > resolution, would be good to see it answered there: > > Oracle SQL DATE conversion problem using iBATIS via Java > JDBC<http://stackoverflow.com/questions/383783/oracle-sql-date-conversion-problem-using-ibatis-via-java-jdbc> > > Here's the problem description: > > I'm currently wrestling with an Oracle sql DATE conversion problem using > iBATIS from Java. > > Am using the Oracle JDBC thin driver ojdbc14 version 10.2.0.4.0. iBATIS > version 2.3.2. Java 1.6.0_10-rc2-b32. > > The problem revolves around a column of DATE type that is being returned by > this snippet of SQL: > > SELECT * > FROM TABLE(pk_invoice_qry.get_contract_rate(?,?,?,?,?,?,?,?,?,?)) order > by from_date > > The package procedure call returns a ref cursor that is being wrapped in a > TABLE to where is then easy to read the result set as though were a select > query against a table. > > In PL/SQL Developer, one of the columns returned, FROM_DATE, of SQL DATE > type, has precision to time of day: > > Tue Dec 16 23:59:00 PST 2008 > > But when I access this via iBATIS and JDBC, the value only retains > precision to day: > > Tue Dec 16 12:00:00 AM PST 2008 > > This is clearer when displayed like so: > > Should have been: > 1229500740000 milliseconds since epoch > Tuesday, December 16, 2008 11:59:00 PM PST > > But getting this instead: > 1229414400000 milliseconds since epoch > Tuesday, December 16, 2008 12:00:00 AM PST > (as instance of class java.sql.Date) > > No matter what I try, I am unable to expose the full precision of this DATE > column to be returned via Java JDBC and iBATIS. > > What iBATIS is mapping from is this: > > FROM_DATE : 2008-12-03 : class java.sql.Date > > The current iBATIS mapping is this: > > <result property="from_date" jdbcType="DATE" javaType="java.sql.Date"/> > > I've also tried: > > <result property="from_date" jdbcType="DATETIME" javaType="java.sql.Date"/> > > or > > <result property="from_date" jdbcType="TIMESTAMP" > javaType="java.sql.Timestamp"/> > > But all attempted mappings yield the same truncated Date value. It's as > though JDBC has already done the damage of loosing data precision before > iBATIS even touches it. > > Clearly I'm loosing some of my data precision by going through JDBC and > iBATIS that is not happening when I stay in PL/SQL Developer running the > same SQL snippet as a test script. Not acceptable at all, very frustrating, > and ultimately very scary. > > >
