[gdal-dev] Data type of DATE in SpatiaLite

[Even Rouault]

Le mardi 21 avril 2015 23:00:48, Jukka Rahkonen a écrit :
> 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""
> 

Jukka,

The GPKG spec also mentions that the declared column data type that must be 
used is "DATE" (or "DATETIME"), that is the one used in the CREATE TABLE 
statement : CREATE TABLE my_table ( DATE my_date, ...)

This name in SQLite can be anything, since you can store anything in a SQLite 
cell. See "Type affinity" in https://www.sqlite.org/datatype3.html

On reading, the SQLite driver will currently only accept data with stored as 
text (speaking here about the content of each cell, not the declared column 
type!) and will try to parse them as ISO-8601. If the values are stored as 
floating point or integer values, it will report null. It might be good to 
implement the rule at 1.2 Date and Time Datatype of 
https://www.sqlite.org/datatype3.html to convert from Julian days or Unix 
time.

And AFAIR the SQLite/Spatialite driver must do something similar as you noted. 
This is practical to use a specific type name rather than a generic one like 
TEXT, since you can know the field type of each field.

> 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?

Honestly I didn't read carefully the whole thread you mention, but it seems 
there's no rule imposed by Spatialite (I should rather the tools in the 
Spatialite ecosystem, since Spatialite itself doesn't manage at all non-
spatial columns, as the name implies). You might have to use appropriate SQL 
functions related on datetimes to transform between Julian days or ISO-8601.

Even

-- 
Spatialys - Geospatial professional services
http://www.spatialys.com