[BUG] - SQLite DATETIME column is incorrectly parsed

[FunnyPocketBook]

First of all, thank you so much for creating this wonderful and beautiful piece of software! I've been looking for something like this for ages but nothing has hit the right spot until now. Very much looking forward to where this goes!

Describe the bug

Background

SQLite technically only has the datatypes TEXT, NUMERIC, INTEGER, REAL, and BLOB, but due to its type affinity, we can use pretty much anything from other database engines, as long as the table is not STRICT.

This means that columns can also have the datatype DATETIME, which would be stored as a TEXT, so a string literal - usually in the ISO 8601 format (YYYY-MM-DD HH:MM:SS) to make it compatible with built-in SQLite functions. However, because it's stored as a string, we can put in whichever format we want it to be, even something cursed like HH:MM:SS DD-YYYY-MM that is not automatically parseable by most functions. Some apps might depend on the specific string as which the datetime has been stored as.

Bug

WhoDB parses dates and datetimes (I think here?), which is nice for database engines that enforce the datatypes, but not for SQLite with "dynamic typing". Any "normal" datetime string (e.g. 2023-05-17 21:54:13.838 +00:00 or 2022-11-17 09:55:00.000000 gets parsed into YYYY-MM-DDTHH:MM:SS.MSZ format (e.g. 2024-08-22T22:15:07.924614Z).

So when using WhoDB to look at the exact data that is stored, or even when using WhoDB to edit the data, the format will be YYYY-MM-DDTHH:MM:SS.MSZ, which is not necessarily what is wanted.

It is also not possible to edit the data and using the custom format, as this error will pop up: Unable to update the row: ApolloError: failed to convert value for column 'last_modified': invalid datetime format: parsing time "2024-08-22 22:15:07.739390" as "2006-01-02T15:04:05Z07:00": cannot parse " 22:15:07.739390" as "T"

To Reproduce

Steps to reproduce the behavior:

1. Create an SQLite 3 non-STRICT table with a DATETIME column.
2. Put in some data that is not in the form of YYYY-MM-DDTHH:MM:SS.MSZ, e.g. 2022-11-17 09:55:00.000000
3. Open the SQLite database in WhoDB and inspect the newly entered data

Expected behavior

I expect the data not to be parsed and shown as it is stored in the DB.

Screenshots

WhoDB screenshot:

sqlite3 CLI output:
1|3|2|2024-08-22 22:15:07.739390|2024-08-22 22:15:07.741091

Desktop (please complete the following information):

• OS: Windows 11 Version 10.0.22631 Build 22631
• Browser: Firefox 134.0b10
• WhoDB Version: 0.44.0

[modelorona]

hi @FunnyPocketBook thank you for the nice feedback :) and the issue as well!

We'll sort this and get back with a PR.