SQLite doesn't have native DateTime datetypes, so setting Maybe UTCTimes results in invalid data

Thanks for working on Selda! It's been a lot of fun to work with it!

The following example SQLite query for casting UTCTimes to DATETIMEs fails:

 select CAST ("2023-02-10 06:02:55" as DATETIME);

As far as I understand it, this is because SQLite doesn't support anything beyond text and numbers. Other databases, like MySQL support the cast just fine!

The code to insert the invalid data is roughly the following:


data Person = Person {
  pk :: ID Person,
  birthday :: Maybe UTCTime -- having this be just a `UTCTime` inserts okay, it's that CAST as DATETIME because of `just` that causes issues
} deriving (Generic, Show)
instance SqlRow Challenge

persons = Selda.table "person" [#pk :- autoPrimary]

main = do
  time <- getCurrentTime
  withSQLite "selda_db.db" <|
    update
      persons
      (\p -> p #pk .== literal 1)
      (\p -> p with [ #birthday :- just (literal time) ])

Which creates a query that looks something like

UPDATE `persons` SET `birthday` = CAST($1 AS DATETIME) WHERE (`pk` = $2)

What's interesting is that if you have birthday be a regular unwrapped UTCTime field, Selda doesn't do any casting, so the values insert just fine. (It would just do SET `birthday = $1, since it doesn't go through just/cast)

Maybe I'm doing something wrong though, and there's a nice solution that I should be using!

valderman / selda

SQLite doesn't have native DateTime datetypes, so setting Maybe UTCTimes results in invalid data #186