Cannot parse all timestamps returned by PostgreSQL

[ysangkok]

This script reliably fails, even though it is doing nothing wrong. It can be run with cabal run ./Test.hs.

{- cabal:
   build-depends: base, HDBC, HDBC-postgresql, time, convertible
-}
module Main where
import Data.Time
import Database.HDBC
import Database.HDBC.PostgreSQL
import Data.Convertible
main = do
  c <- connectPostgreSQL "host=/var/run/postgresql user=janus password=lol"
  runRaw c "set timezone to 'America/Mexico_City';" -- UH OH
  select <- prepare c "SELECT ? :: timestamptz;"
  execute select [toSql $ (convert (UTCTime (fromGregorian 1920 12 25) 0) :: SqlValue)]
  result <- fetchAllRows select
  putStrLn $ show result
  commit c

The issue seems to be that if the timezone offset that comes back has sufficient granularity, it cannot be parsed by the Convertible instance:

 % cabal run ./Test.hs
Resolving dependencies...
Build profile: -w ghc-9.0.1 -O1
In order, the following will be built (use -v for more details):
 - fake-package-0 (exe:script) (configuration changed)
Configuring executable 'script' for fake-package-0..
Preprocessing executable 'script' for fake-package-0..
Building executable 'script' for fake-package-0..
[1 of 1] Compiling Main             ( Main.hs, /home/janus/flipstone/hdbc-postgresql/dist-newstyle/build/x86_64-linux/ghc-9.0.1/fake-package-0/x/script/build/script/script-tmp/Main.o )
Linking /home/janus/flipstone/hdbc-postgresql/dist-newstyle/build/x86_64-linux/ghc-9.0.1/fake-package-0/x/script/build/script/script ...
script: Convertible: error converting source data SqlString "1920-12-24 17:23:24-06:36:36" of type SqlValue to type ZonedTime: Cannot parse using default format string "%Y-%m-%d %T%Q %z"
CallStack (from HasCallStack):
  error, called at ./Data/Convertible/Base.hs:66:17 in convertible-1.1.1.0-612d414cafd18c253ab58ea430e579d9acf3584c43d4fcee9b6e216dac83fa55:Data.Convertible.Base

It seems like HDBC-postgresql doesn't set a session timezone, instead it relies on whatever the session ends up using. Since my system timezone is America/Mexico_City, the error is triggered for me even without manually setting the timezone in the connection. And if I set the timezone to UTC in the script, there is no problem either.

If you change to 1930 instead of 1920, there is no crash. It is probably related to the date at which Mexico adopted time zones.

My preferred solution would be that HDBC-postgresql parses all timestamps returned by PostgreSQL, even if they have weird offsets. But one workaround could be that HDBC-postgresql sets the timezone to UTC on opening the connection. I dunno if that would be considered confusing. I actually thought it was more confusing that the system timezone is used at all.

I am using PostgreSQL 12 from Ubuntu 20.04, and the latest HDBC-postgresql from Hackage for GHC 9.0.1.

If you'd like me to attempt at submitting a PR that fixes the problem, I can take a stab at it. But please tell me if you'd like the workaround or not.

[dmjio]

If you'd like me to attempt at submitting a PR that fixes the problem, I can take a stab at it. But please tell me if you'd like the workaround or not.

Workaround sounds good, along with attempting to use UTCTime.