[gdal-dev] Data type of DATE in SpatiaLite

[Pepijn Van Eeckhoudt]

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

That’s correct. The intention is that gpkg authors only use declared column types that are listed in the spec (see req 5 and table 1). This is to allow correct interpretation of columns when exchanging gpkg files between systems.

We restricted the DATE and DATETIME format to a single ISO 8601 variant for each for the same reason. It would makes things too complicated and error prone when exchanging files if any date were allowed. The only downside to this is a performance hit compared to julian time as REAL or unix timestamp as INTEGER since the ISO 8601 string needs to be parsed.

> 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 <http://www.gdal.org/drv_sqlite.html> about how GDAL plays
> with them once I understand it myself.

That was my conclusion a year or two ago as well :) That’s what led to the current text in gpkg.

The same applies for the other declared column types as well. Earlier revisions of the spec didn’t have the type table which resulted in one vendor using int32, int64, float32, float64, etc; another using int, bigint, float, double and so on. Restricting numeric types to a single list again simplifies things and improves interoperability.
The sizes are intended as hints for applications. The idea is that if a column is declared as FLOAT that you can assign values from this column to a 32-bit floating point variable in Java, C, C#, etc without losing precision. Without this everything has to be 64-bit.

Pepijn
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20150422/47f91dfd/attachment.html>