[Geotools-gt2-users] Mapping DATE to java.sql.Timestamp in OracleDialect (gt-jdbc-oracle)

[Peter K. <pet...@mi...>]

Dear GeoTools comunity,

I'm struggling to make ImageMosaic work with time dimension when using 
Oracle database. I'm not quite sure if this should go to the devel 
mailing list or to the issue tracker directly so I'm using this list as 
a starting point for discussion.


The following schema (an excerpt from the indexer.xml file) does not 
work with Oracle datastore (however, it works perfectly in Postgres):

       <schema name="default">
<attributes>*the_geom:Polygon,location:String,time:java.util.Date</attributes>
       </schema>

The problem is that my data have date AND time, but /something /between 
GeoServer and the database itself truncates the time part.

After digging into the GeoServer and GeoTools code I found out my main 
suspect. I believe that the existing mapping between Oracle database 
types and Java types in the org.geotools.data.oracle.OracleDialect class 
in 
gt-jdbc-oracle/src/main/java/org/geotools/data/oracle/OracleDialect.java 
is the cause. On line 165 (in current geotools 16.x branch) it maps the 
DATE type to the java.sql.Date class. As we all know, java.sql.Date does 
not store the time information.

In my opinion, this mapping is incorrect. The correct mapping should be 
DATE to java.sql.Timestamp, because the DATE type in Oracle database 
includes the time down to a second. The current Oracle JDBC driver maps 
DATE to java.sql.Timestamp by default, as stated on their website (they 
used to have it wrong too, but fixed it): 
http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#08_01

> ...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 commit which introduced the DATE ->java.sql.Date mapping looks like 
it was a deliberate decision ( 
https://github.com/geotools/geotools/commit/ba023c75ebd13279c3beb69721bfd361bf3a6908 
https://osgeo-org.atlassian.net/browse/GEOT-3216 ). Maybe changing the 
mapping is not a good idea after all (I don't know what might break in 
the GeoTools universe).

So, I tried to change my schema to the following:

       <schema name="default">
<attributes>*the_geom:Polygon,location:String,time:java.sql.Timestamp</attributes>
       </schema>

Unfortunately, it creates the /same /database table, with the DATE type 
for the time column. Maybe if this schema would create a TIMESTAMP 
column, then the default JDBC mapping would be from TIMESTAMP to 
java.sql.Timestamp and the time information would be retained.

When figuring out why java.sql.Timestamp is translated to the DATE 
database column, I found out that:

* initially, there is no mapping from java.sql.Timestamp to any Oracle 
data type (org.geotools.jdbc.JDBCDataStore.getSQLTypeNames, lines 3126 
to 3156). the integer value to map is 93 ( 
http://docs.oracle.com/javase/8/docs/api/constant-values.html#java.sql.Types.TIMESTAMP 
)
* the metaData.getTypeInfo() (line 3198) is used to get default database 
type mappings
* the type java.sql.Timestamp gets mapped to DATE database type in an 
interation of the following while loop


To sum up:

1)  Oracle JDBC driver mapsjava.sql.Timestamp to the DATE database type 
because, well, it's theirs decision (if there is any millisecond 
information in the Timestamp it would be lost I guess, but who wants 
milliseconds anyway?).

2) GeoTools maps DATE database type to java.sql.Date type, apparently 
because of backwards compatibility issues.

Therefore, when creating ImageMosaic with time dimension and some actual 
time values, the features-to-be-inserted are converted from 
java.sql.Timestamp to java.sql.Date and the time information is lost.


What can we do about it? Is it better to discard the DATE -> 
java.sql.Date mapping and leave it all to Oracle JDBC defaults; or we 
should add another mapping saying java.sql.Timestamp should go to the 
TIMESTAMP database type? Or do nothing, because there is some hidden 
Oracle JDBC switch which achieves the desired mapping and I'm just not 
aware of it?

I'm looking forward to any suggestions.


I found all of this while working with GeoServer 2.10.0 and GeoTools 16. 
My Oracle version is 12cR1, I'm using the ojdbc7.jar, my Java is 
jdk1.8.0_102, everything runs in Tomcat 8.5.4 and I'm using the 
-Doracle.jdbc.J2EE13Compliant=true property to get java.sql.Timestamp 
instead of oracle.sql.TIMESTAMP when using SQL Views in GeoServer.



Cheers,

Peter

-- 
Peter Kovac
IMS Programmer
MicroStep-MIS
pet...@mi...