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

[Jukka Rahkonen]

 <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

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?

Regards,

-Jukka Rahkonen-