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.

[nathanfallet]

@mickael-menu Feel free to open a PR to fix this issue, we will be happy to review it and merge it!

[mickael-menu]

@NathanFallet I'm not using SQLite.swift anymore

[mickael-menu]

(... literally one year later)

@NathanFallet I switched to GRDB.swift as SQLite.swift was not maintained anymore at the time. However, that's great that you've taken over maintenance. There's definitely a space and need for a lightweight SQLite Swift wrapper. Keep up the good work!

[alcortazzo]

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.

Thank you for your reply! It solved my problem.

I just want to add more context to this issue to make it more relevant in Google search so that more people can find it.

I had this problem because I created and populated the sqlite3 database for my iOS Swift application using SQLAlchemy (Python). SQLAlchemy uses a string in the following format to represent DateTime objects: "%Y-%m-%d %H:%M:%S.%f" (reference). This is how SQLAlchemy stores dates in the sqlite3 DB. But when I tried to select records from sqlite3 DB using SQLite.swift, I got error in this code

    public static func fromDatatypeValue(_ stringValue: String) -> Date {
        dateFormatter.date(from: stringValue)! // <--- Thread 1: Fatal error: Unexpectedly found nil while unwrapping an Optional value
    }

This error occurs because SQLite.swift is trying to parse DateTime string in sqlite3 DB using this format "%Y-%m-%dT%H:%M:%S.%f". So when i changed dateFormatter.dateFormat in my swift code to "yyyy-MM-dd HH:mm:ss.SSS", the error was fixed.