[gdal-dev] Data type of DATE in SpatiaLite

[a.furieri at lqt.it]

Jukka, Pepjin and Even,

AFAIK there is not a clearly "better" way to store DATE values
(TIME,  DATETIME, TIMESTAMP and alike) in a SQLite DB-file.

* SQLite practically ignores any datatype definition placed
   at the column level; real datatypes are bound to each
   single cell, and can be one between TEXT, INTEGER, DOUBLE,
   BLOB or NULL.
   for any practical effect DATE or TIMESTAMP (exactly as any
   other "exotic" datatype not directly handled by SQLite) will
   simply default to TEXT.

* a full functional equivalent of DATE/TIME types as usually
   available on more conventional DBMSes is anyway supported,
   but requires a rather unconventional approach.
   you can internally store any DATE/TIME value in the form
   of Julian Numbers (actually a DOUBLE), then calling one
   of the several conversion functions supported by SQLite.
   it works nicely well, but completely depends on user or
   application actions. the DB engine itself is completely
   agnostic and doesn't offers any special support.

* the alternative approach is the one to directly store
   text strings formatted accordingly to ISO-8601.
   this choice has the obvious advantage to present human
   readable values, anyway explicitly calling the appropriate
   conversion SQL function will be always required in order
   to perform any arithmetic operation based on time
   (e.g. computing the number of days elapsed between two
   dates and alike).

both solutions seems to be roughly equivalent: both are
really far from perfection, both are intrinsically weak.
in ultimate analysis all depends on user/application
actions and on conventional assumptions; that's not what
you are expecting from a sane-minded DBMS.

a possible "universal" encoding schema to be applied
by good willing reader appa:

- when a column is declared as DATE, DATETIME etc
   - if the cell value is DOUBLE assume a Julian Number
     and apply the appropriate conversion then returning
	an ISO-8601 string.
   - if the cell value is TEXT assume ISO-8601.
     if the value does not pass formal validation
     then return NULL
   - return NULL in any other case

bye Sandro