It seems that the writer wrongly rounds the values of seconds greater than 59.
Using DB Browser to query the result:
Could it be a bug?
Best answer by chrisatsafe
View original
Datetime field issue in Geopackage writer
+2
- Contributor
- 606 replies
-
28 September 2021
It looks like the rounding is correct in this case. GeoPackage uses ISO-8601 datetime format and in this case it is being rounded at the third decimal place %Y%m%d%H%M%S%z. Since your value is .99996 it appears to round up. If you truncate everything after the third decimal place (or round down) you won't run into this issue.
There is very detailed answer regarding rounding datetimes in this Question of the Week if you are interested in learning more! In short, it's not a bug; rather it's the nature of binary floating-point arithmetic.
Thanks for you answer @chrisatsafe. But regardless of the number of decimal places, I believed that 60 is not a valid value for seconds according to ISO-8601. So I understand that FME should be aware of the seconds valid range in transformations. Seconds values like 59.999638 can be generated by the Now() function in PostgreSQL for a timestamptz field. In a direct transformation from PostgreSQL to GeoPackage, it seems reasonable for the user to assume that the datetime rounding performed by the GeoPackage writer is correct.
+2
- Contributor
- 606 replies
-
29 September 2021
- Best Answer
Thanks for you answer @chrisatsafe. But regardless of the number of decimal places, I believed that 60 is not a valid value for seconds according to ISO-8601. So I understand that FME should be aware of the seconds valid range in transformations. Seconds values like 59.999638 can be generated by the Now() function in PostgreSQL for a timestamptz field. In a direct transformation from PostgreSQL to GeoPackage, it seems reasonable for the user to assume that the datetime rounding performed by the GeoPackage writer is correct.
I had a bit of a deeper dive into this and it turns out there is something called leap seconds. Leap seconds allow for the value to be 60 seconds but this should only valid on a couple of days (June 30 and December 31). This appears to be more of a floating point issue which is discussed in more detail this thread (with what looks like a workaround for the time being).
The GeoPackage writer enforces 3 decimal places on seconds and attempts to parse and manage this using our standard datetime handling (we use this on both read and write).
We have an outstanding request for this in our system which I have linked this post to and an idea has been posted to create a new transformer called to help handle date/time rounding.
Reply
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.