Thanks for the detailed email, this one is a bit tough to sneak up on as we
are hitting the JDBCDataStore code (for OracleDialect) and ImageMosaic code.
Does anyone else have experience on the difference between Date and
Timestamp to draw on?
Thanks for testing with the latest release.
--
Jody Garnett
On 8 November 2016 at 11:06, Peter Kovac <[email protected]>
wrote:
> 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 maps java.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
> [email protected]
>
>
> ------------------------------------------------------------
> ------------------
> Developer Access Program for Intel Xeon Phi Processors
> Access to Intel Xeon Phi processor-based developer platforms.
> With one year of Intel Parallel Studio XE.
> Training and support from Colfax.
> Order your platform today. http://sdm.link/xeonphi
> _______________________________________________
> GeoTools-GT2-Users mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/geotools-gt2-users
>
>
------------------------------------------------------------------------------
Developer Access Program for Intel Xeon Phi Processors
Access to Intel Xeon Phi processor-based developer platforms.
With one year of Intel Parallel Studio XE.
Training and support from Colfax.
Order your platform today. http://sdm.link/xeonphi
_______________________________________________
GeoTools-GT2-Users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geotools-gt2-users
