[gdal-dev] Data type of DATE in SpatiaLite

[Jukka Rahkonen]

Hi,

After short investigation it looks that ogr2ogr is writing DATE data into
Spatialite database by creating a field that is defined to be of type "DATE"
and by inserting there string values in format "2006-12-21".

This is not what current native SpatiaLite tools do. They use either text
strings placed into TEXT field or Julian Day numbers stored into a DOUBLE
field. Details about why in the forum thread
https://groups.google.com/d/msg/spatialite-users/lvd7uPJ3Big/TKNPiG6IqsIJ

>From the GeoPackage specification I can read about how to store DATE:

"ISO-8601 date string in the form YYYY-MM-DD encoded in either UTF-8 or
UTF-16. See TEXT. 
Stored as SQLite TEXT"

DATETIME is to be stored as SQLite TEXT as well:
"ISO-8601 date/time string in the form YYYY-MM-DDTHH:MM:SS.SSSZ with T
separator character and Z suffix for coordinated universal time (UTC)
encoded in either UTF-8 or UTF-16. Stored as SQLite TEXT""

It might be good to uniform the behaviour and make also the
SQLite/Spatialite driver to use a TEXT field for DATE. But how GDAL could
then recognize DATE data when it reads Spatialite? How it is handled in GPKG
driver?

-Jukka Rahkonen-