Skip to content

internal JDBC: inaccuracies converting TIMESTAMP and TIMESTAMPTZ #155

@jcflack

Description

@jcflack

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.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions