[gdal-dev] Data type of DATE in SpatiaLite

[Jukka Rahkonen]

Even Rouault <even.rouault <at> spatialys.com> writes:

> 
> 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, ...)

You are right even I can't find an example of using CREATE table...DATE from
the specification. However, the CREATE clauses for the system tables do use
DATETIME and I can imagine that the meaning is that DATE should be  used
similarly. Timestamps are created as

last_change DATETIME NOT NULL DEFAULT
(strftime('%Y-%m-%dT%H:%M:%fZ','now')),

The changelog of standard shows that TEXT was considered first 

2013-10-15 R9 Paul Daisey 2.4.3.1.1, Annex C Change timestamp column from
TEXT to DATETIME

It would be totally clear for everybody if the "Table 25
sample_feature_table Table Definition SQL (Informative)" in the page 104 of
the standard was including also own rows for date and timestamp columns but
it does not. Anyway, there is a good reason to believe that DATE and
DATETIME should be used.
 
> 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
> 

I know this and my mail was one consequence. I was testing an application
that is checking the schema with "PRAGMA table_info(table)" and when it
finds a DATE columns it awaits data to be in "DD/MM/YYYY" format. Chancing
the declared data type into TEXT makes this application happy and therefore
it can open Spatialite databases which are created with Spatialite-gui but
not the ones which are created with ogr2ogr.

With GPKG the situation is simple because both the declared column type and
the form of the date string are defined but for Spatialite "DD/MM/YYYY" and
"YYYY-MM-DD" and "foo_&_2_%bar" are all as acceptable as dates. But the
trouble I had comes from a bug in that application and I know how to go
around it.

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

I agree and I think that on the Spatialite side it might be good to make the
spatialite tools to declare the columns which are known to contain
dates/times as DATE/DATETIME even those types do not really mean anything
for SQLite and huge amounts of existing and future Spatialite databases hold
dates and times in columns which are declared as TEXT, INTEGER, DOUBLE, or
without a type at all.

All in all, handling dates is the typeless SQLite in a reliable and
interoperable way seems to be a bit complicated. I will think about writing
a line or two into http://www.gdal.org/drv_sqlite.html about how GDAL plays
with them once I understand it myself.

-Jukka-