Sqlx SQLite expiring sessions at wrong time

[osmano807]

I was experiencing premature session deletion, even with a file database. I traced the issue as incorrect date handling in the query, as SQLite returns wrong values with datetime('now', 'utc').

https://github.com/maxcountryman/tower-sessions-stores/blob/51cc75817cb98e875d8cf3975363f746857ad0b2/sqlx-store/src/sqlite_store.rs#L78

See the following thread on StackOverflow with someone with the same bug https://stackoverflow.com/questions/57052204/utc-time-wrong-in-sqlite

Using the SQLite docs, it appears to be safe to use only datetime('now')

https://www.sqlite.org/lang_datefunc.html

Format 11, the string 'now', is converted into the current date and time as obtained from the xCurrentTime method of the sqlite3_vfs object in use. The 'now' argument to date and time functions always returns exactly the same value for multiple invocations within the same sqlite3_step() call. Universal Coordinated Time (UTC) is used.

[maxcountryman]

Well that's an unfortunate design.

Happy to accept a PR to address.

[osmano807]

I'll need to investigate more, as the table is created with expiry_date integer and apparently it's storing a text (SQLite doesn't respect types).

[Katze864]

SQLite just compares the string literals of the time, so they need to be formatted the same way. datetime('now') is formatted as YYYY-MM-DD HH:MM:SS, while the dates inputted from the rust are formatted as YYYY-MM-DDTHH:MM:SS.SSSSSSSZ. SQLite datetime accepts different string formats, but there does not seem to be an option to format the output: https://www.sqlite.org/lang_datefunc.html. A working selection could be WHERE datetime(expiry_date) < datetime('now').