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 _______________________________________________ gdal-dev mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/gdal-dev
