On Tue, 30 Sep 2014 12:49:50 +0200, Even Rouault wrote:
> 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
>
Even, Jukka,
the patch #5672 seems to definitively resolve this issue.
> 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.
>
yes, that's true.
SQLite doesn't support real DATE / TIME / DATETIME / TIMESTAMP
datatypes:
we are necessarily forced to use DOUBLE or TEXT as a possible
replacement.
a) the DOUBLE way
SQLite nicely supports Julian Day Numbers (JDN) [1]
a date(time) expressed as a JDN always is a floating point value;
the integer part represents the number of days passed since the
origin
of the time (March 1, 4801 BC) and the fractional part represents
the number of seconds passed since midnight.
PROS: you can directly perform arithmetic operations on behalf of
JDN values
CONS: it surely isn't a "natural" notation; the end user is forced
to always use some SQL function in order to properly format
an "human readable" date/time value
anyway SQLite support plenty of date/time formatting functions
[1] http://en.wikipedia.org/wiki/Julian_day
b) the TEXT way
alternatively you can directly store a text string (possibily
formatted accordingly to ISO8601)
PROS: this way all data(time) are directly "human readable";
and you can safely apply comparison operators as e.g.
SELECT * FROM a WHERE day > '1950-01-15' AND day <
'1950-02-07';
CONS: the end user will be anyway forced to call some conversion
function in order to perform aritmetic operations on behalf
of date/time values.
malformed values (not at all a remote possibily when using an
uncenstrained text string) could easily lead to odd results.
> 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
>
SpatiaLite adopts the opposite approach when importing from Shapefiles;
JDN is the preferred option.
Anyway the latest 4.2.1 allows the user to freely choice between the
JDN and the textual interpretation. please see this (very long) thread:
https://groups.google.com/forum/#!searchin/spatialite-users/jdn/spatialite-users/lvd7uPJ3Big/9x38y32-cGYJ
> Those data types are likely not understood by spatialite-gui.
>
there is no problem at all from the spatialite-gui side; users will
simply be required to call conversion functions if and when they'll
eventually whish to perform arithmetic operations on dates.
there is no other practical consequence.
bye Sandro