Spring R2DBC DatabaseClient losing TZ information

Question

I have a db entity with some Instant type fields. org.springframework.data.r2dbc.core.DatabaseClient (which is now deprecated), had a method .as(..) to automatically map to a java entity and it honoured the timezones too. Not sure how that happened internally.

But using org.springframework.r2dbc.core.DatabaseClient which does not have an automatic mapper, I must use a .map(...) which provides the Rows and I map them like

row.get("blah", Instant.class) But it just gives time in my local TZ, not UTC.

Anyone knows the root cause? Thanks.

score 2 · Answer 1 · answered Jun 19 '21 at 23:07

Short Answer

I'm assuming that you are using the PostgresSQL database and appropriate driver. Also, I assume that you are using the TIMESTAMP DB type for your Instant filed. To get the correct timezone from org.springframework.r2dbc.core.DatabaseClient you can use TIMESTAMP WITH TIME ZONE as a data type.

Long Answer

I have created a test repo to test the behavior of R2dbcRepository and R2DBC DatabaseClient to retrieve timestamp and timestampz into Instant. To do so, I have created the following table: CREATE TABLE test_table (id SERIAL PRIMARY KEY, timestamp_without_tz TIMESTAMP, timestamp_with_tz TIMESTAMP WITH TIME ZONE);

And implemented the following service:

package com.patotski.r2dbctimestamps.service;


import com.patotski.r2dbctimestamps.domain.TestEntity;
import com.patotski.r2dbctimestamps.repo.TestEntityRepo;
import lombok.RequiredArgsConstructor;
import org.springframework.r2dbc.core.DatabaseClient;
import org.springframework.stereotype.Service;
import reactor.core.publisher.Mono;

import java.time.Instant;

@Service
@RequiredArgsConstructor
public class TestEntityService {

    private final TestEntityRepo testEntityRepo; // uses standard R2dbcRepository implementation
    private final DatabaseClient databaseClient;

    public Mono<TestEntity> saveUsingRepo(TestEntity entity) {
        return testEntityRepo.save(entity);
    }

    public Mono<TestEntity> getByIdFromRepo(int id) {
        return testEntityRepo.findById(id);
    }

    public Mono<TestEntity> saveUsingDbClient(TestEntity entity) {
        return databaseClient.sql("INSERT INTO test_table (timestamp_without_tz, timestamp_with_tz) VALUES(:timestamp_without_tz, :timestamp_with_tz)")
                .bind("timestamp_without_tz", entity.getTimestamp_without_tz())
                .bind("timestamp_with_tz", entity.getTimestamp_with_tz())
                .map(row -> TestEntity.builder()
                        .id(row.get("id", Integer.class))
                        .timestamp_with_tz(row.get("timestamp_with_tz", Instant.class))
                        .timestamp_without_tz(row.get("timestamp_without_tz", Instant.class))
                        .build()).first();
    }

    public Mono<TestEntity> getByIdFromDbClient(int id) {
        return databaseClient.sql("SELECT * from test_table where id = :id")
                .bind("id", id)
                .map(row -> TestEntity.builder()
                        .id(row.get("id", Integer.class))
                        .timestamp_with_tz(row.get("timestamp_with_tz", Instant.class))
                        .timestamp_without_tz(row.get("timestamp_without_tz", Instant.class))
                        .build()).first();
    }

}

And created tests that just store entity and retrieve asserting Timestamps:

@SpringBootTest
class TestEntityServiceTest {

    @Autowired
    TestEntityService testEntityService;

    @Test
    @DisplayName("Should store and retrieve Entity with both timestamp fields in correct timezones using R2DBC repo.")
    void shouldStoreCorrectTimestampsAndRetriveWithRepo() {
        Instant now = Instant.now();
        TestEntity entity = TestEntity.builder()
                .timestamp_with_tz(now)
                .timestamp_without_tz(now)
                .build();

        TestEntity saved = testEntityService.saveUsingRepo(entity).block();

        Assertions.assertThat(testEntityService.getByIdFromRepo(saved.getId()).block()).isNotNull()
                .extracting(TestEntity::getId,
                        TestEntity::getTimestamp_without_tz,
                        TestEntity::getTimestamp_with_tz)
                .containsExactly(saved.getId(), now, now);
    }

    @Test
    @DisplayName("Should store and retrieve Entity with both timestamp fields in correct timezones using R2DBC DatabaseClient.")
    void shouldStoreCorrectTimestampsAndRetriveWithDbClient() {
        Instant now = Instant.now();
        TestEntity entity = TestEntity.builder()
                .timestamp_with_tz(now)
                .timestamp_without_tz(now)
                .build();

        testEntityService.saveUsingDbClient(entity).block();

        Assertions.assertThat(testEntityService.getByIdFromDbClient(1).block()).isNotNull()
                .extracting(TestEntity::getId,
                        TestEntity::getTimestamp_without_tz,
                        TestEntity::getTimestamp_with_tz)
                .containsExactly(1, now, now);
    }
}

Results show that these 2 methods are not consistent with each other:

R2dbcRepository test always passes in all TZs
DatabaseClient test passes only when tests are running in UTC TZ and fails when not. The reason is that field defined as TIMESTAMP in DB gets TZ offset.
TIMESTAMPZ field works correctly in both cases always

Repo to reproduce: https://github.com/xp-vit/r2dbc-timestamps

Created and issue for Spring team to at least discuss: https://github.com/spring-projects/spring-data-r2dbc/issues/608

Spring R2DBC DatabaseClient losing TZ information

1 Answers1