4

I am using H2 in-memory database in Spring integration test.

I have following column on my entity

    @Column(nullable = false)
    private LocalDateTime lastUpdateDateTime;

However I noticed some problems in H2, sometimes it truncates nanoseconds part eg:

Saved datetime: 2020-07-02T13:36:40.459145400

After few moments 2020-07-02T13:36:40.459145

It seems that H2 is not able to store nanoseconds and just round up nanoseconds part.

My integration tests cannot pass because of this inconsistency. Could You please help ?

Tomasz Bieniek
  • 81
  • 4
  • 1
    H2 can store nanoseconds, but you need `TIMESTAMP(9)` column for that purpose. The `TIMESTAMP` data type without parameter holds only microseconds as required by the SQL Standard. – Evgenij Ryazanov Jul 03 '20 at 09:22
  • I'm having the same problem and adding `@Column(columnDefinition = "TIMESTAMP(6)")` didn't resolve it. Did you ever find a solution? – Erik Goepfert Sep 01 '22 at 20:32

2 Answers2

2

This boils down to nanoseconds resolution. Java has greater nanoseconds resolution (6 digits) than H2 (9 digits).

For me TIMESTAMP(9) did the trick:

@Column(name = "creationDate", nullable = false, columnDefinition = "TIMESTAMP(9)")
    val creationDate: LocalDateTime,
Igor
  • 2,039
  • 23
  • 27
0

I have a solution to avoid the truncation of nanosaconds, when LocalDateTime would be persisted into the H2 database. I used Hibernate 5.x and H2 database 2.1.214.
Firstly, an inherited class would be extended from org.hibernate.dialect.H2Dialect like below.

package com.example;
    
import org.hibernate.dialect.H2Dialect;

import java.sql.Types;
    
public class ExtendedH2Dialect extends H2Dialect {
    public ExtendedH2Dialect() {
        registerColumnType(Types.TIMESTAMP, "timestamp(9)");
    }
}

Then, the above class would be set as hibernate dialect property.

hibernate.dialect=com.example.ExtendedH2Dialect

In this case, the production code is untouched.

I hope it help. Best Regards.

Rol
  • 1