UTCTime wrong format in SQLite

[kuerzn]

UTCTime values inserted into SQLite Databases look like this
2014-11-30 05:15:25.395343 UTC

This does not conform to the specification of an SQLite Date Value:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS")

(from https://www.sqlite.org/datatype3.html)

and consequently, queries like the following produce no output:

> select date("2014-11-30 05:15:25.395343 UTC")

expected output:

> select date("2014-11-30 05:15:25")
2014-11-30

[snoyberg]

Can you provide an example of using the API that has bad output, so that it can be used for an automated test?

[kuerzn]

{-# LANGUAGE TemplateHaskell #-}
{-# LANGUAGE TypeFamilies #-}
{-# LANGUAGE MultiParamTypeClasses, GeneralizedNewtypeDeriving, OverloadedStrings, QuasiQuotes, GADTs                #-}

import           Data.Time
import           Database.Persist.Sqlite
import           Database.Persist.TH

share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persistLowerCase|
Test
  time UTCTime
|]


main :: IO Bool
main = runSqlite ":memory:" $ do
  runMigration migrateAll
  insert . Test $ read "2014-11-30 05:15:25.123"
  [Single x] <- rawSql "select strftime('%s%f',time) from test" []
  return $ x == Just ("141732452525.123" :: String)

[snoyberg]

This should be resolved on master, can you test it before I release to Hackage? Thanks for the minimal reproducing case.

[snoyberg]

If you're using cabal sandbox, then add-source pointed at the persistent-sqlite folder should be sufficient.

[kuerzn]

Yes it works now! Thanks

[snoyberg]

Cool, thanks to both of you for confirming. I've released this code to
Hackage. I'm having issues connecting to Github, so if you could close this
issue for me, it would be appreciated.

On Mon Dec 08 2014 at 10:15:55 PM kuerzn notifications@github.com wrote:

Yes it works now! Thanks

—
Reply to this email directly or view it on GitHub
#328 (comment).