lundi 6 juillet 2015

Convert UTCTime to CSV field with Haskell/Cassave

I started learning Haskell and I try to query data from a SQLite file and dump it again as CSV. I have an issue formating UTCTime fields, so that Cassave can format it correctly. This is my type:

    module History.Types
      (Visit)
      where

    import Data.ByteString as B
    import Data.Csv
    import Data.Text (Text)
    import Data.Time
    import Data.Time.Format
    import Database.SQLite.Simple.FromRow

    data Visit = Visit
      { url :: Text
      , title :: Text
      , visit_count :: Int
      , typed_count :: Int
      , last_visit_time :: UTCTime
      , visit_time :: UTCTime
      , from_visit :: Int
      }
      deriving (Show)

    instance FromRow Visit where
      fromRow = Visit <$> field
                      <*> field
                      <*> field
                      <*> field
                      <*> field
                      <*> field
                      <*> field

    instance ToField UTCTime where
      toField t = formatTime defaultTimeLocale "%d-%m-%Y %H:%M:%S" t

    instance ToRecord Visit where
      toRecord (Visit url
                      title
                      visit_count
                      typed_count
                      last_visit_time
                      visit_time
                      from_visit) = record [ toField visit_time
                                           , toField url
                                           , toField title
                                           , toField visit_count
                                           , toField typed_count
                                           , toField from_visit
                                           , toField last_visit_time
                                           ]

And my SQLite code:

    {-# LANGUAGE OverloadedStrings #-}

    module History.DB
      (queryHistory)
      where

    import History.Types (Visit)

    import Data.Text (Text)
    import Database.SQLite.Simple (open, query_, close, Query)

    q :: Query
    q = "\
    \SELECT urls.url \
    \     , urls.title \
    \     , urls.visit_count \
    \     , urls.typed_count \
    \     , datetime(urls.last_visit_time/1000000-11644473600,'unixepoch','localtime') \
    \     , datetime(visits.visit_time/1000000-11644473600,'unixepoch','localtime') \
    \     , visits.from_visit \
    \FROM urls, visits \
    \WHERE urls.id = visits.id \
    \ORDER BY visits.visit_time"

    queryHistory :: FilePath -> IO [Visit]
    queryHistory dbPath =
      do conn <- open dbPath
         history <- query_ conn q
         close conn
         return history

Compiling this code leads to the following error:

    src/History/Types.hs:34:15:
        Couldn't match type ‘[Char]’ with ‘ByteString’
          Expected type: Field
          Actual type: String
        In the expression:
          formatTime defaultTimeLocale "%d-%m-%Y %H:%M:%S" t
        In an equation for ‘toField’:
          toField t = formatTime defaultTimeLocale "%d-%m-%Y %H:%M:%S" t

So clearly I make a mess with when formatting the date type into a string. I look at the type information of formatTime and don't really understand why my String (I assume the error relates to the date formatting string) has to be a ByteString. So my questions are:

  1. Is the error related to the time formatting string, and if yes, why is a regular string not working?
  2. Is this the right way to format date types to be written to a CSV file using Cassave?

Aucun commentaire:

Enregistrer un commentaire