So, nobody is using GeoTools with time dimension on top of Oracle?
Nobody is having difficulties with times cut off? Or are you guys just
busy and didn't have time to look at this issue (I can totally
understand that)?
I'd like to know if the community considers this behavior a bug or a
"feature" (for the sake of backwards compatibility). For me, the current
state of things is unacceptable. I'll have to patch it in the source
somehow or switch to PostGIS. The thing is, maybe a patch that works for
me won't work for somebody else. So I'd like to come up with a patch
that is acceptable to others as well and which can be included in the
GeoTools source tree. I think that will be the best for everybody involved.
And that's why I'm writing again, to ask the community if my proposed
solutions are okay or not...
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.
On 9. 11. 2016 23:11, Jody Garnett wrote:
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] <mailto:[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
<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://github.com/geotools/geotools/commit/ba023c75ebd13279c3beb69721bfd361bf3a6908>
https://osgeo-org.atlassian.net/browse/GEOT-3216
<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
<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
[email protected] <mailto:[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]
<mailto:[email protected]>
https://lists.sourceforge.net/lists/listinfo/geotools-gt2-users
<https://lists.sourceforge.net/lists/listinfo/geotools-gt2-users>
--
Peter Kovac
IMS Programmer
MicroStep-MIS
[email protected]
------------------------------------------------------------------------------
_______________________________________________
GeoTools-GT2-Users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geotools-gt2-users
