Broken FromField UTCTime instance for some strange old timestamps

[cvb]

Tested with master and some older version, here is the error:

> query_ conn "select '1500-01-01T10:10:10.00Z'::timestamptz" :: IO [Only UTCTime]
*** Exception: ConversionFailed {errSQLType = "timestamptz", errSQLTableOid = Nothing, errSQLField = "timestamptz", errHaskellType = "UTCTime", errMessage = "endOfInput"}

Seems that parser don't know how to deal with seconds in timezone.

cvb=# select '1500-01-01'::timestamptz;
         timestamptz
------------------------------
 1500-01-01 00:00:00+02:30:20
(1 row)

Can someone suggest any workaround for this?

[lpsmith]

Yes, I know of this problem. You should probably read the discussion in #69. I think you'll find it enlightening.

I haven't been motivated to fix it because I doubt many people are impacted by it. (And actually, for dates hundreds of years in the past, the timezone database isn't really accurate, and probably cannot be accurate. My limited understanding is that at that point in history the notion of time was sufficiently muddled that you really need a specialist historian to even interpret what day it is, as the precise context can make a difference of up to a few weeks.)

As for properly supporting offsets with seconds in them, we would have to get away from ZonedTime to some degree or another, which assumes that timezones represent a fixed offset (hah!) and that offsets are always an integer number of minutes. So there is no possible way the ZonedTime instance will ever support these dates without modifying the ZonedTime type itself, which is difficult if not impossible to do without breaking a fair bit of code. We could support UTCTime with this, but it won't be a simple conversion through ZonedTime as it basically is now.

Alternatively, if all you care about is converting to UTCTime, and don't want to use postgresql's timezone handling, you can simply do a SET timezone TO 'utc', which sets the timezone used for that connection. Then all timestamptz's on that connection will be returned with a zero offset, and Haskell doesn't have to worry about that conversion at all.

[cvb]

Thank you for explanation, I think SET timezone TO 'utc' will do the trick, anyway such dates is some kind of mistake in user input.

[lpsmith]

Ok, thanks to a bit of friendly haranguing from Michael Snoyman, I did end up committing a proper fix for this. As soon as I release the latest commit onto Hackage, there will no longer be any need to SET timezone TO 'utc' for converting very old timestamps to UTCTime without error. However, ZonedTime will still choke on these timestamps, and I don't see any good way to fix this. See issues #123 and #124.