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?
Solved
Datetime field issue in Geopackage writer
+8- Contributor
- 44 replies
Best answer by chrisatsafe
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.
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
+2- Contributor
- 606 replies
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.
+8- Contributor
- 44 replies
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
- 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.