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.

Environment

None

Attachments

1

Activity

Andrea Aime 
June 27, 2024 at 1:40 PM
(edited)

Didn't this fix too?

Jody Garnett 
May 30, 2024 at 5:42 AM

Thanks - you are welcome to review the PR, or try out the result in a nightly build once it is available.

Stijn Goedertier 
May 29, 2024 at 8:51 PM

Many thanks for the PR, David. OK to include the test case in the geotools codebase.

David Blasby 
May 28, 2024 at 11:04 PM

I’ve made a PR for this -

I’ve included Stijn’s (??) test case. Is this ok to add to the GT codebase?

This PR needs a good view - the Datetime stuff is pretty easy to get it subtly incorrect.

 

Thanks!

Fixed

Details

Assignee

Reporter

Components

Fix versions

Affects versions

Priority

Created February 27, 2024 at 8:13 AM
Updated June 27, 2024 at 1:40 PM
Resolved June 4, 2024 at 7:21 PM