[bug] Wrong timestamp read to Instant

[Paramood]

Describe the bug If reading timestamp field as Instant, it gets wrong value but write Instant to timestamp works fine.

To Reproduce Map timestamp field on Instant Java type. E.g. database stores 2020-09-11 14:42:32 but when reading as Instant, the obtained value is 2020-09-11T11:42:32Z. This looks like it is doing some timezone offset.

Expected behavior The obtained value must be the same as the value in database.

Additional context This was fine in version 1.0.2 and it is broken in 1.0.3.

[jchrys]

Thanks for the report

[jchrys]

@Paramood I've failed to reproduce the issue you described. (Below test passed)

        final String tdl = "CREATE TABLE testTimestamp(id INT PRIMARY KEY AUTO_INCREMENT,value TIMESTAMP)";
        complete(connection ->
                         Mono.from(connection.createStatement(tdl).execute())
                             .flatMap(IntegrationTestSupport::extractRowsUpdated)
                             .then(Mono.from(
                                     connection.createStatement("INSERT INTO testTimestamp(value)VALUES('2023-10-01 12:23:34')")
                                               .execute()))
                             .flatMap(IntegrationTestSupport::extractRowsUpdated)
                             .then(Mono.from(connection.createStatement("SELECT value FROM testTimestamp").execute()))
                             .flatMap(result -> Mono.from(result.map((row, metadata) -> row.get(0, Instant.class))))
                             .doOnNext(value -> assertThat(value).isEqualTo(Instant.parse("2023-10-01T12:23:34Z")))
        );

Could you provide us more detailed information(do you use spring-data-r2dbc?, what is your connection url?) or ideally please spend some time providing a minimal sample that reproduces the problem?

[Paramood]

I've tried your example and the test is passed. It seems that the problem begins if using spring-data-r2dbc. Here is an example of test that passes with io.asyncer:r2dbc-mysql:1.0.2 but fails with 1.0.3:

dbtest.zip

My result is:

reactor.core.Exceptions$ReactiveException: org.opentest4j.AssertionFailedError: 
expected: 2023-10-01T12:23:34Z
 but was: 2023-10-01T09:23:34Z

It seems that it shifts the time against timezone offset of my OS. I tried to change OS timezone and the obtained time from DB have also changed.

[jchrys]

@Paramood Thanks a lot! Please utilize version 1.0.4 or 0.9.5, which incorporates necessary fixes.

[Paramood]

@jchrys Thank you for the fix!