Hibernate 5.6.9 @Version annotation requires precision 9 for timestamp

[Michael Brizic]

Does anyone know why using H2 on Java 15+ and Spring/Hibernate with entitys that are versioned using timestamps require a precision of 9 instead of 6?

We upgraded our web app to use Java 17. Since Java 15+ the JDK/JRE support nanosecond precision for datetime objects. Our integration tests use H2 version 1.4.200. 

It seems I have needed to change our entities from:
@Version
@Column(columnDefinition = “DATETIME(6)”)
private Instant version;

To:

@Version
@Column(columnDefinition = “DATETIME(9)”)
private Instant version;

Otherwise I will get optimistic locking failed; nested exception is org.hibernate.StaleObjectStateException

[Evgenij Ryazanov]

Hello!

Resolution of system timestamps in JVM depends on Java version and operating system. Old versions of Java (Java 8 and older) provide only 3 digits. Resolution was improved in Java 9, this version provides 7 digits on Windows, but only 6 digits on Linux, because it uses an old system function with this limitation. Java 15 and newer versions use a modern function and provide 9 digits instead of 6 on Linux.

There are two data types in the SQL Standard and H2 for datetime values with both date and time parts: TIMESTAMP and TIMESTAMP WITH TIME ZONE. Both data types have default fractional seconds precision of 6 as required by the Standard and some database systems, including the H2, support larger precision.

(DATETIME is silently replaced with standard TIMESTAMP data type, actually you normally should use TIMESTAMP(9) WITH TIME ZONE for Instant values in H2 to avoid issues on DST or other time zone transitions.)

Instant values in Java also support up to 9 fractional digits. When you insert a value with non-zero nanoseconds into a column with lower fractional seconds precision, this value is rounded to that precision by H2 (the SQL Standard doesn't specify an exact behavior, it only requires an implementation-defined rounding or truncation). When Hibernate reads it back it gets a rounded value instead of expected original one. To avoid it, you must define an explicit fractional seconds precision of 9 for this column.

[Michael Brizic]

Hi Evgenij,

Thanks for the explanation, very helpful!

Wondering if you could would know any additional details ... With respect to "Hibernate reads it back it gets a rounded value instead of expected original one" do you have any further details as to exactly HOW this fails if for example, the timestamps are indeed different or is this a problem only when the timestamp differences are microsecond or nanoseconds apart? Is there some kind of hashing and equals checks that fail somehow internally? In other words, initial insert using nanoseconds but rounded to microsecods is followed by an update some microseconds later ... wouldn't those two timestamps be different despite the rounding that occurred?