ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Richard Yee <r...@cruzio.com>
Subject Re: Oracle SQL DATE conversion problem using iBATIS via Java JDBC
Date Sun, 21 Dec 2008 08:14:57 GMT

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


      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


      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 
V8Compatible not 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 


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

View raw message