H2 database default value of TIMESTAMP column

Question

I am writing integration tests with H2 database. My database (generated) initialization include this script (because generated join table does not have this column):

ALTER TABLE INT_USR ADD IU_INSDTTM TIMESTAMP DEFAULT NOW();

This is how I create records:

Integration integrationOne = createIntegration(firstId, "FIRST");
Integration integrationTwo = createIntegration(secondId, "SECOND");
flushAndClear();
userService.logRecentIntegration(integrationOne.getId(), user.getId());
flushAndClear();
userService.logRecentIntegration(integrationTwo.getId(), user.getId()); //1

The method logRecentIntegrations(.., ..) just calls the DAO and the dao does this:

Query query = entityManager.createNativeQuery(
    "INSERT INTO INT_USR (USR_ID, INT_ID) VALUES (?, ?)");
query.setParameter(1, userId)
    .setParameter(2, integrationId);
query.executeUpdate();

Later in my test:

Query query = entityManager.createNativeQuery(
    "SELECT * FROM INT_USR ORDER BY IU_INSDTTM");
List resultList = query.getResultList();

When I debug this test in resultList there are two records (correct) but they have same timestamp. Even when I inserted a breakpoint on line marked //1 and waited a while - so the time gap between inserts would be significant. (Thread.sleep - same result)

I tried to modify the SQL script to

ALTER TABLE INT_USR ADD IU_INSDTTM TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

But with same result. Why both results have same timestamp?

Answer 1

As documented, the function CURRENT_TIMESTAMP always returns the same value within a transaction. This behavior matches other databases, for example PostgreSQL.