System.Data.SQLite

Please provide an additional column value type based mapping of SQLiteDateFormats. The existing DateTimeFormat setting should be used both as ultime fallback if there exists no type based mapping and as the preferred way of storing new values, so it would not change anything to existing connection strings which don't include a special INTEGER/REAL/TEXT mapping.

From the 5 possible SQLite types (NULL, INTEGER, REAL, TEXT, BLOB) only INTEGER, REAL and TEXT would be relevant:
  SQLiteDateFormats.Ticks  ->  INTEGER
  SQLiteDateFormats.ISO8601 -> TEXT
  SQLiteDateFormats.JulianDay -> REAL
  SQLiteDateFormats.UnixEpoch -> INTEGER (or REAL if "whole number of seconds" is not correct)
  SQLiteDateFormats.InvariantCulture -> TEXT
  SQLiteDateFormats.CurrentCulture -> TEXT
  SQLiteDateFormats.Default -> ???  (I have no clue what "The default format for this provider." means.)

Example:
  var csb = new SQLiteConnectionStringBuilder
    {
      DateTimeFormat = SQLiteDateFormats.JulianDay, // Fallback & new values
      DateTimeFormatREAL = SQLiteDateFormats.JulianDay, // could be implicitely determined by the Fallback value
      DateTimeFormatTEXT = SQLiteDateFormats.ISO8601,
      DateTimeFormatINTEGER = SQLiteDateFormats.Ticks,
      [..]
    };

That way it would allow reading values of tables where the storage format has been changed over time either for the entire table or even just row by row or -- of course this would not work for different text formattings.


I'm requesting this because my database versioning table contains a "datetime" column so currently in order to read it by the newer software version I would have to migrate the data before I could check (and ask the user) if an update migration would be needed; which already would break older versions even if the user decides not to upgrade the DB.

[I changed from ISO8601 (TEXT) to JulianDay (REAL) because this saves a lot of disk space (=more data per page) and text parsing CPU time.]

This is a feature request.  It's probably too late in the release cycle for this
type of thing to get into 1.0.95.0; however, I'll evaluate it carefully for the
next release after that.