`ToField UTCTime` sub-microsecond rounding

[avieth]

Postgres will parse the time part after the decimal point to a double, then multiply by 1 million, then round to an integer. This can lead to some strange behaviour:

ghci> query conn "select (?)::timestamptz" (Only (UTCTime (fromGregorian 1970 1 1) 0.0000025)) :: IO [Only UTCTime]
[Only {fromOnly = 1970-01-01 00:00:00.000002 UTC}]

That one appears to round down, but this one appears to round up

ghci> query conn "select (?)::timestamptz" (Only (UTCTime (fromGregorian 1970 1 1) 0.5187445)) :: IO [Only UTCTime]
[Only {fromOnly = 1970-01-01 00:00:00.518745 UTC}]

because

ghci> ((0.5187445 :: Double) * (1000000 :: Double))
518744.50000000006

Arguably ToField UTCTime shouldn't exist, since it can't really fit into a timestamptz. But that's probably not an acceptable breaking change so what else could be done about this? Maybe instance ToField UTCTime should truncate to microseconds?

[ysangkok]

I think it would be best to have a separate time type that accurately represents what PostgreSQL has. This would also allow for a time zone offset granularity of minutes, which PostgreSQL has, but which the time library doesn't support.

For example, try something like

SET timezone='Africa/Monrovia';
select '1950-01-01' :: timestamptz;

[phadej]

As with #157 the separate type can be provided as a library, postgresql-libpq is not a good place for experimentation.

Related to original issue, I dont' see how truncating UTCTime will help. We'll "control" how rounding happens, but how that would make anything significantly different and better?

IMO, the solution "just don't use too precise UTCTime" (or don't care about rounding) is good. postgresql-libpq instances are not perfect (e.g. Text shouldn't include \0 code point - Postgres cannot represent that), and trying to make them perfect would make usage too inconvenient.