GeoPackage TIMESTAMP must be DATETIME according to the GeoPackage spec
Description
Timestamp columns in GeoPackages written with geotools 30.2 cannot be read by GDAL/OGR nor by QGIS. The reason for this is that the sqlite datatype of these columns is TIMESTAMP instead of DATETIME.
According to requirement 5 of the GeoPackage specification (see http://www.geopackage.org/spec/#r5), timestamp columns must have the DATETIME datatype. The values must be an “ISO-8601 date/time string in the form YYYY-MM-DDTHH:MM[:SS.SSS]Z with T separator character, Z suffix for coordinated universal time (UTC), and encoded in either UTF-8 or UTF-16.”
The attached junit test based on GeoPackageTest demonstrates that geotools will output a geopkg.gpkg with TIMESTAMP columns.
If we inspect the output with ogr2ogr, we get the warning that the TIMESTAMP column is not supported. It is missing from the output!
If we add another column with the same values, but DATETIME datatype using sqlite3, the fixed column is read by ogr2ogr and included in the output, but we get another warning that its value is not conformant to the GeoPackage spec (not of the form YYYY-MM-DDTHH:MM[:SS.SSS]Z).
If we format the timestamp correctly, and drop the original column created by geotools, the warnings disappear.
Timestamp columns in GeoPackages written with geotools 30.2 cannot be read by GDAL/OGR nor by QGIS. The reason for this is that the sqlite datatype of these columns is TIMESTAMP instead of DATETIME.
According to requirement 5 of the GeoPackage specification (see http://www.geopackage.org/spec/#r5), timestamp columns must have the DATETIME datatype. The values must be an “ISO-8601 date/time string in the form YYYY-MM-DDTHH:MM[:SS.SSS]Z with T separator character, Z suffix for coordinated universal time (UTC), and encoded in either UTF-8 or UTF-16.”
The attached junit test based on GeoPackageTest demonstrates that geotools will output a geopkg.gpkg with TIMESTAMP columns.
If we inspect the output with ogr2ogr, we get the warning that the TIMESTAMP column is not supported. It is missing from the output!
If we add another column with the same values, but DATETIME datatype using sqlite3, the fixed column is read by ogr2ogr and included in the output, but we get another warning that its value is not conformant to the GeoPackage spec (not of the form YYYY-MM-DDTHH:MM[:SS.SSS]Z).
If we format the timestamp correctly, and drop the original column created by geotools, the warnings disappear.