1

I have a MariaDB database which I query through Haskell HDBC. In it I store a datetime column in UTC time. I would like to retrieve the values as SqlUTCTime and perform the conversion to UTCTime using fromSql.

  • If I retrieve the values directly in SELECT, I get an SqlLocalTime which can not be converted to UTCTime by fromSql.
  • If I try to use DATE_FORMAT() in SQL to get ISO8601 compliant strings (adding trailing +00:00 or Z to indicate UTC), I get errors that the strings are not compliant.

I ultimately managed to convert manually from SqlLocalTime to SqlUTCTime :

convertSqlValue :: SqlValue -> Maybe UTCTime
convertSqlValue sql = case sql of
    SqlString s -> iso8601ParseM s
    SqlUTCTime u -> Just u
    SqlLocalTime l -> Just $ localTimeToUTC utc l
    other -> Nothing

where the SqlLocalTime is called and localTimeToUTC utc l performs the conversion.

My question therefore is: what should I do to get an SqlUTCTime directly ?

vkubicki
  • 1,104
  • 1
  • 11
  • 26
  • The documentation indicates that `SqlUTCTime` is YYYY-MM-DD HH:MM:SS. Perhaps you could try using `DATE_FORMAT` to produce that, rather than an 8601-compliant string, and see what happens. – Daniel Wagner Mar 17 '23 at 21:34
  • DATE_FORMAT(cards.last_valid, '%Y-%m-%d %H:%i:%s') is not recognized as a SqlUTCTime. – vkubicki Mar 17 '23 at 21:50

0 Answers0