[JPP-Devel] DB Query Plugin, SpatiaLite, GPKG and DATE data type

[Rahkonen J. (MML) <juk...@ma...>]

Hi,

The SQLite/Spatialite/GeoPackage driver of the DB Query Plugin is reading data from a column that is recognized as a DATE with this code that comes from http://sourceforge.net/p/jumpdbqplugin/code/ci/default/tree/src/main/java/org/freevoice/jumpdbqueryextension/spatialite/JumpSpatialiteDbQuery.java (line 252)

 else if (AttributeType.DATE.equals(attributeType))
      {
         returnObject = new Date(results.getTimestamp(dbColumnIndex).getTime());
      }

I found that the code gives an error if dates in the database are encoded into ISO text format, like "2015-04-23".  After some experiments I started to think that results.getTimestamp(dbColumnIndex).getTime() awaits a number, perhaps an integer number, maybe UNIX seconds.

It would be better to consider that all times in those databases (DATE, TIME, TIMESTAMP, DATETIME) are ISO strings. If date is not a valid ISO data string they it could be skipped and converted into NULL for OpenJUMP. That would be more user friendly than to stop parsing and quit.

More details about times in SQLite based databases in gdal-dev thread http://thread.gmane.org/gmane.comp.gis.gdal.devel/40172 

-Jukka Rahkonen-