Sqlite.swift: Parsing default SQLite DATETIME format into Date

[mickael-menu]

Hi,

I've been trying to read a DATETIME column using an Expressible<Date>, unfortunately it crashes in Date.fromDatatypeValue.

(lldb) po stringValue
"2020-02-05 18:10:14"

(lldb) po dateFormatter.date(from: stringValue)
▿ Optional<Date>
  ▿ some : 2001-01-01 00:00:00 +0000
    - timeIntervalSinceReferenceDate : 0.0

I think that SQLite.swift is expecting a Date format which is not the one used by default in SQLite by DATETIME("now") or CURRENT_TIMESTAMP.

The problem is that I rely on SQLite to automatically fill my created and updated column using CURRENT_TIMESTAMP, I guess that if I was using Date through SQLite.swift it would work fine, but I prefer to keep that kind of things in the database.

created DATETIME DEFAULT(CURRENT_TIMESTAMP) NOT NULL

CREATE TRIGGER IF NOT EXISTS trigger_objects_updated AFTER UPDATE ON objects
BEGIN
    UPDATE objects SET updated = CURRENT_TIMESTAMP WHERE id = new.id;
END;

Is there any workaround, or should this be fixed in SQLite.swift's dateFormatter?

Build information: SQLite.swift 0.12.2, Xcode 11.3.1

[mickael-menu]

Thankfully, SQLite.dateFormatter is a var and is public, so I could override the dateFormat in my app.

import SQLite

dateFormatter.dateFormat = "yyyy-MM-dd HH:mm:ss"

However, I think SQLite.swift should be able to parse all the formats defined in the SQLite documentation for the date and time functions (date, time, datetime and julianday), and serialize the Date objects into the most precise of SQLite default formats: datetime. Which is the one I used above.

I'm willing to write a PR, but this repo doesn't seem very active anymore. Let me know if you're interested.