How do you use timestamps with postgresql-simple?

[commandodev]

Here's my table:

CREATE TABLE event (
      event_id uuid PRIMARY KEY NOT NULL
    , event_timestamp timestamp
    , event json
  );

At the moment I've got a type like this:

data Event e = Event {
     event_id        :: UUID
   , event_timestamp :: UTCTime -- This is the problem
   , event           :: e
   } deriving (Show)

instance FromField UUID where
  fromField f mdata =
    if typeOid f /= builtin2oid UUID
        then returnError Incompatible f ""
        else case fromString . C8.unpack <$> mdata of
               Nothing  -> returnError UnexpectedNull f ""
               Just dat -> case dat of
                    Nothing -> returnError ConversionFailed f "Couldn't convert to UUID"
                    Just x -> return x

instance FromJSON a => FromRow (Event a) where
  fromRow = Event <$> field --(fromJust . fromByteString <$> field)
                  <*> field
                  <*> (fromJust . decode <$> field)

instance ToJSON a => ToRow (Event a) where
  toRow Event {..} = [
                       toField (toString event_id)
                     , toField event_timestamp
                     , toField $ encode event
                     ]

I tried changing the UTCTime to a UTCTimestamp

I managed to get as far as inserting a row:

select * from event;
               event_id               |      event_timestamp       |     event      
--------------------------------------+----------------------------+----------------
 df2c7eb5-39d2-4dc3-b1c7-2ac9f8884330 | 2013-08-21 06:02:02.131355 | {"name":"Ben"}

However selecting with:

selectEvents :: Connection -> IO [Event NameEvent]
selectEvents con = query_ con "SELECT * FROM event"

gives:

 Exception: Incompatible {errSQLType = "timestamp", errSQLTableOid = Just (Oid 24594), errSQLField = "event_timestamp", errHaskellType = "UTCTime", errMessage = ""}

or when I use UTCTimestamp:

Exception: Incompatible {errSQLType = "timestamp", errSQLTableOid = Just (Oid 24594), errSQLField = "event_timestamp", errHaskellType = "UTCTimestamp", errMessage = ""}

Any idea what I might be doing wrong here?

BTW any insight into getting UUID to work with ByteStrings would also be appreciated - I'm sure a round trip through String can't be a good thing!

[lpsmith]

UTCTime is only compatible with timestamp with time zone. As far as out-of-box instances are concerned, only LocalTime and Database.PostgreSQL.Simple.Time.LocalTimestamp is compatible with timestamp without time zone.

Also, timestamp with time zone is almost universally misunderstood. You might want to read the discussion on issue #69 for an explanation.

As for parsing UUIDs directly from bytestrings, that would depend on whether or not the underlying library supports it. Last I was aware, neither of the uuid packages on hackage has a parser for bytestrings. Of course, you could always write a bytestring parser yourself, outside the library. If you do, you might try to open a pull request for the library in question.

[lpsmith]

Ok, I guess that the uuid package did add a fromByteString, so you could just use that in the FromField instance instead. Like you did in your FromRow instance.

Also, both the Conversion and RowParser monads implement a sensible fail operation, so you can use that instead of fromJust. That will route the error through the error-handling mechanisms those monads provide, and let you use <|> to recover from a failed parse instead of throwing a pattern match exception.

[commandodev]

Thanks @lpsmith.

I changed the column to timestamptz and the code worked fine.

I tried the toByteString initially, but postgres didn't like the bytes. Perhaps I need to encode it to utf-8 or something?

You mention the Conversion and RowParser monads - is there any documentation on which to use and where?

Thanks
Ben

[lpsmith]

I tried the toByteString initially, but postgres didn't like the bytes. Perhaps I need to encode it to utf-8 or something?

Uhh, in your FromField instance, or your ToField instance? I don't really know why this would be off the top of my head. You'd have to investigate that yourself.

You mention the Conversion and RowParser monads - is there any documentation on which to use and where?

You are already using them: Conversion is the monad the fromField method runs in, RowParser is what the fromRow method runs in. Perhaps the documentation could be better, but they are documented in the FromField and FromRow modules respectively.

So for example, you could define your own

myFromJust Nothing = fail ""   -- (or fail "error message")
myFromJust Just x = return x

and use it in your fromField and fromRow definitions.