[gdal-dev] About DateTime detection (bug somewhere)

[Even Rouault]

Le mardi 30 septembre 2014 12:02:10, Jukka Rahkonen a écrit :
>  <a.furieri <at> lqt.it> writes:
> > On Mon, 29 Sep 2014 21:44:30 +0000 (UTC), Jukka Rahkonen wrote:
> > > Hi,
> > > 
> > > I noticed http://trac.osgeo.org/gdal/changeset/27768 and by looking
> > > at the
> > > different date and time formats I remembered that I have just
> > > received some
> > > data where dates were expressed as "2014-09-27T" which lead to some
> > > trouble.
> > > 
> > > I do not know if it is valid to end date with "T" without giving time
> > > as well.
> > 
> > Hi Jukka,
> > 
> > doesn't seems to be a valid format accordingly to ISO 8601
> > specifications:
> > 
> > "the 'T' appears literally in the string, to indicate the beginning of
> > the time element"
> > 
> > please see: http://www.w3.org/TR/NOTE-datetime
> > 
> > bye Sandro
> 
> Hi Sandro,
> 
> Funny coincident, dates as "2014-09-27T" appear just in Spatialite when I
> convert some shapefiles with ogr2ogr.
> 
> I tried to find out what happens by myself but it is somehow odd so here
> are some data for reproducing the issue:
> http://latuviitta.org/downloads/Date_with_ending_T.zip
> 
> Convert data into Spatialite with ogr2ogr like
> ogr2ogr -f SQLite -dsco spatialite=yes test.sqlite test.shp
> 
> The problematic field is named "timestamp" and the datatype in shapefile is
> Date. In the resulting Spatialite db the CREATE statement seems to create
> the column as 'timestamp' DATE.
> 
> Now see what happens if I query this field with SQL either from
> spatialite-gui or from command window with "spatialite.exe"
> 
> spatialite> select "timestamp" from test limit 5;
> 2013-12-19T
> 2013-12-19T
> 2013-12-19T
> 2013-12-19T
> 2013-12-19T

Jukka, Sandro,

Upon investigation, the T suffix was added by the OGR SQLite driver. I've 
removed it in trunk for now per http://trac.osgeo.org/gdal/ticket/5672

> 
> I made some tests and noticed that if I import shapefile with
> spatialite-gui the "timestamp" column is marked to type "double" and for
> reading dates I must query it as SELECT date("timestamp")...
> 
> The dates with ending "T" make real headache for me because the program I
> use for reading data from Spatialite does not accept such dates and I must
> read all those tables through views.
> 
> Perhaps you two, Even and Sandro, could chat a moment and think about what
> would be the best way for converting dates into Spatialite?

The general issue is the lack of SQLite of strong typing, and the multiple 
ways allowed by SQLite in §1.2 of http://www.sqlite.org/datatype3.html to 
express dates.
The OGR driver text storage with ISO8601 strings, and uses the following 
custom typenames: TIMESTAMP (for OFTDateTime), DATE (for OFTDate) and TIME 
(for OFTTime). That way it can correctly round-trips the data types. The 
content of records are respectively YYYY-MM-DDTHH:MM:SS, YYYY-MM-DD (after the 
fix) and HH:MM:SS

Those data types are likely not understood by spatialite-gui.

Even

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