-
Notifications
You must be signed in to change notification settings - Fork 77
Description
The things you discover if you write some generic code to roundtrip values from SQL through Java and back.
select orig = roundtripped as good, * from
(values (timestamp '2017-08-21 18:25:29.900005')) as p(orig),
roundtrip(p) as r(roundtripped timestamp);
good | orig | roundtripped
------+----------------------------+----------------------------
f | 2017-08-21 18:25:29.900005 | 2017-08-21 18:25:30.800005
(1 row)
I believe this is because the Java classes specified by JDBC to represent the values break the value into a milliseconds-since-epoch part and a nanoseconds-of-second part that redundantly includes the sub-second milliseconds part. The roundtripped value comes back as if the .900 part has been double-counted—not the whole .900005, but only the milliseconds.
For values before the (PostgreSQL or Java) epoch, it's worse:
select orig = roundtripped as good, * from
(values (timestamp '1970-03-07 17:37:49.300009Z')) as p(orig),
roundtrip(p) as r(roundtripped timestamp);
ERROR: java.lang.IllegalArgumentException: nanos > 999999999 or < 0
select orig = roundtripped as good, * from
(values (timestamp '1919-05-29 13:08:33.600001Z')) as p(orig),
roundtrip(p) as r(roundtripped timestamp);
ERROR: java.lang.IllegalArgumentException: nanos > 999999999 or < 0
These reveal that the specified Java class insists on the nanos component being positive, regardless of the sign of the millis-since-epoch component. That's helpful in that it eliminates some of the cases where C90 leaves signed / and % implementation-defined, but it still requires attention in the C code to ensure that the 'floored' variety of / and % is consistently what's being used.