Spring-data-jdbc microsecond precision lost during insert of LocalDateTime type variable

Question

It seems to me that when we run repository.save(entity) and the entity has a LocaldateTime variable mapped to a column of type timestamp(6), spring-data-jdbc (or some underlying library) is rounding off the data inserted in the column to 3 decimal points and appending zeros

Below is the simple CustomerOrder entity I plan to insert

public class CustomerOrder {

@Id
String id;

LocalDateTime createdAt;

}

Here is the schema

CREATE TABLE IF NOT EXISTS customer_order (
    id char(36) NOT NULL PRIMARY KEY ,
    created_at timestamp(6)
);

The CRUD repo interface extension

@Repository
public interface OrderRepository extends CrudRepository<CustomerOrder, String> {
}

The configuration class to set the ID

@Configuration
public class SampleAppConfig {

    @Bean
    public ApplicationListener<?> idAndTimeStampSetting() {
        return new ApplicationListener<BeforeSaveEvent>() {
            @Override
            public void onApplicationEvent(BeforeSaveEvent beforeSaveEvent) {
                var entity =beforeSaveEvent.getEntity();
                if ( entity instanceof  CustomerOrder ){
                    CustomerOrder customerEntity = (CustomerOrder) entity;
                    customerEntity.setId(UUID.randomUUID().toString());
                }
            }
        };
    }
}

The app

@SpringBootApplication
public class SampleApp {
    public static void main(String[] args) {
        SpringApplication.run(SampleApp.class, args);
    }
}

The Test case

@RunWith(SpringRunner.class)
@SpringBootTest
class CustomerOrderRepositoryTest {
    @Autowired
    OrderRepository repository;
    @Test
    public void testFind() {
        // create entity to insert
        CustomerOrder expectedCustomerOrder = new CustomerOrder();
        expectedCustomerOrder
              .setCreatedAt(LocalDateTime.now().truncatedTo(ChronoUnit.MICROS));

        // run the insert
        repository.save(expectedCustomerOrder);
        
        // run findall to get everything
        List<CustomerOrder> allActualOrders = StreamSupport.stream(repository.findAll().spliterator(), false).collect(Collectors.toList());
        
        //validate data
        assertEquals(1, allActualOrders.size());
        assertEquals(expectedCustomerOrder, allActualOrders.get(0));
    }
}

Test Case output

org.opentest4j.AssertionFailedError: 
Expected :CustomerOrder(id=c8a9fc0e-ee9a-4798-b273-323b3a922a43, createdAt=2020-11-18T10:44:03.390097)
Actual   :CustomerOrder(id=c8a9fc0e-ee9a-4798-b273-323b3a922a43, createdAt=2020-11-18T10:44:03.390)
<Click to see difference>


    at org.junit.jupiter.api.AssertionUtils.fail(AssertionUtils.java:55)
    at org.junit.jupiter.api.AssertionUtils.failNotEqual(AssertionUtils.java:62)
    at org.junit.jupiter.api.AssertEquals.assertEquals(AssertEquals.java:182)
    at org.junit.jupiter.api.AssertEquals.assertEquals(AssertEquals.java:177)
    at org.junit.jupiter.api.Assertions.assertEquals(Assertions.java:1124)
    at com.test.sample.sdj.CustomerOrderRepositoryTest.testFind(CustomerOrderRepositoryTest.java:36)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:686)
    at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
    at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:149)
    at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:140)
    at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:84)
    at org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)
    at org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
    at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)
    at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$6(TestMethodTestDescriptor.java:212)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:208)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:137)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:71)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:135)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1540)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1540)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:32)
    at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
    at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:51)
    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:248)
    at org.junit.platform.launcher.core.DefaultLauncher.lambda$execute$5(DefaultLauncher.java:211)
    at org.junit.platform.launcher.core.DefaultLauncher.withInterceptedStreams(DefaultLauncher.java:226)
    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:199)
    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:132)
    at com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:69)
    at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)

I have tried writing a custom converter and registering the same to make sure I explicitly convert java.time.LocalDateTime to java.sql.Timestamp . No Joy!

This works when I change the datatype of createdAt from LocalDateTime to Timestamp but that is not what I would like to do for my domain model.

I blame the save instead of findAll here instead of update because I see the records in my database with millisecond precision and appended zeros

This is ran against H2(v1.4.200) and MYSQL (v5.7) .

Any known issues with spring-data-jdbc or any available workarounds?

This looks like a bug to me. Could you create an issue including a reproducer? https://jira.spring.io/projects/DATAJDBC — Jens Schauder, Nov 22 '20 at 16:01
Not related to the issue at hand, but `LocalDateTime` is not suiteable for time stamping since it does not denote a point in time. Uset `Instant` instead. — Jens Schauder, Nov 27 '20 at 13:11
And does `Instant` gets converted to `java.sql.Timestamp`? I wonder why the custom converter didn’t kick in when I tried converting `LocalDateTime` to `java.sql.Timestamp` — Paras Vanjani, Nov 29 '20 at 00:51

score 2 · Answer 1 · answered Nov 27 '20 at 14:37

The problem is that Spring Data JDBC considers Date as the target type to be used with JDBC. But Date only has millisecond precision.

I currently can't think of an easy workaround, this should be fixed in Spring Data JDBC and therefore I created a ticket for it: https://jira.spring.io/browse/DATAJDBC-637

Spring-data-jdbc microsecond precision lost during insert of LocalDateTime type variable

1 Answers1

Linked