java.time.Instant obtained through Arrow Flight JDBC Driver is 16 hours different from the original timestamp

Describe the bug, including details regarding any error messages, version, and platform.

java.time.Instant obtained through Arrow Flight JDBC Driver is 16 hours different from the original timestamp. This was originally discussed at Timestamps queried from InfluxDB 3 Core via JDBC Driver are inconsistent with those inserted influxdata/influxdb#25983 but it looks like there is mishandling within the Arrow Flight JDBC Driver.
I created a minimal reproducible unit test at https://github.com/linghengqian/influxdb-3-core-jdbc-test/blob/master/src/test/java/io/github/linghengqian/TimeDifferenceTest.java . The relevant unit tests use Influxdb 3 core .
To execute it, just install SDKMAN! and Docker CE in advance, then,

sdk install java 21.0.6-ms
git clone git@github.com:linghengqian/influxdb-3-core-jdbc-test.git
cd ./influxdb-3-core-jdbc-test/
sdk use java 21.0.6-ms
./mvnw -T 1C -Dtest=TimeDifferenceTest clean test

Click me to view the core logic of the unit test🥯🥨🍟🧂🥖🥚🍔🦪🍜🍘

@Testcontainers
public class TimeDifferenceTest {

    private final Instant magicTime = Instant.now().minusSeconds(10);

    @Container
    private final GenericContainer<?> container = new GenericContainer<>("quay.io/influxdb/influxdb3-core:911ba92ab4133e75fe2a420e16ed9cb4cf32196f")
            .withCommand("serve --node-id local01 --object-store memory")
            .withExposedPorts(8181);

    @Test
    void test() throws Exception {
        try (InfluxDBClient client = InfluxDBClient.getInstance(
                "http://" + container.getHost() + ":" + container.getMappedPort(8181),
                null,
                "mydb")) {
            writeData(client);
            queryDataByHttp();
            queryDataByJdbcDriver();
        }
    }

    private void writeData(InfluxDBClient client) {
        Point point = Point.measurement("home")
                .setTag("location", "London")
                .setField("value", 30.01)
                .setTimestamp(magicTime);
        client.writePoint(point);
    }

    private void queryDataByHttp() throws URISyntaxException, IOException, InterruptedException {
        URI uri = new URIBuilder().setScheme("http")
                .setHost(container.getHost())
                .setPort(container.getMappedPort(8181))
                .setPath("/api/v3/query_sql")
                .setParameter("db", "mydb")
                .setParameter("q", "select time,location,value from home order by time desc limit 10")
                .build();
        HttpResponse<String> response = HttpClient.newHttpClient()
                .send(HttpRequest.newBuilder().uri(uri).GET().build(), BodyHandlers.ofString());
        assertThat(
                new ObjectMapper().readTree(response.body()).get(0).get("time").asText(),
                is(magicTime.atOffset(ZoneOffset.UTC).toLocalDateTime().toString())
        );
    }

    private void queryDataByJdbcDriver() throws SQLException {
        HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setJdbcUrl("jdbc:arrow-flight-sql://" + container.getHost() + ":" + container.getMappedPort(8181) + "/?useEncryption=0&database=mydb");
        try (HikariDataSource hikariDataSource = new HikariDataSource(hikariConfig);
             Connection connection = hikariDataSource.getConnection()) {
            ResultSet resultSet = connection.createStatement().executeQuery("select time,location,value from home order by time desc limit 10");
            assertThat(resultSet.next(), is(true));
            assertThat(resultSet.getString("location"), is("London"));
            assertThat(resultSet.getString("value"), is("30.01"));
            assertThat(resultSet.getTimestamp("time"), notNullValue());
            // todo linghengqian why fail?
            assertThat(resultSet.getTimestamp("time").toInstant(), is(magicTime));
        }
    }
}

[INFO] Running io.github.linghengqian.TimeDifferenceTest
SLF4J(W): No SLF4J providers were found.
SLF4J(W): Defaulting to no-operation (NOP) logger implementation
SLF4J(W): See https://www.slf4j.org/codes.html#noProviders for further details.
2月 25, 2025 9:17:46 上午 org.apache.arrow.driver.jdbc.shaded.org.apache.arrow.memory.BaseAllocator <clinit>
信息: Debug mode disabled. Enable with the VM option -Darrow.memory.debug.allocator=true.
2月 25, 2025 9:17:46 上午 org.apache.arrow.driver.jdbc.shaded.org.apache.arrow.memory.DefaultAllocationManagerOption getDefaultAllocationManagerFactory
信息: allocation manager type not specified, using netty as the default type
2月 25, 2025 9:17:46 上午 org.apache.arrow.driver.jdbc.shaded.org.apache.arrow.memory.CheckAllocator reportResult
信息: Using DefaultAllocationManager at memory/netty/DefaultAllocationManagerFactory.class
[ERROR] Tests run: 1, Failures: 1, Errors: 0, Skipped: 0, Time elapsed: 3.520 s <<< FAILURE! -- in io.github.linghengqian.TimeDifferenceTest
[ERROR] io.github.linghengqian.TimeDifferenceTest.test -- Time elapsed: 3.457 s <<< FAILURE!
java.lang.AssertionError: 

Expected: is <2025-02-25T01:17:34.640356152Z>
     but: was <2025-02-24T09:17:34.640356152Z>
        at org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:20)
        at org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:8)
        at io.github.linghengqian.TimeDifferenceTest.queryDataByJdbcDriver(TimeDifferenceTest.java:89)
        at io.github.linghengqian.TimeDifferenceTest.test(TimeDifferenceTest.java:50)
        at java.base/java.lang.reflect.Method.invoke(Method.java:580)
        at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
        at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)

[INFO] 
[INFO] Results:
[INFO] 
[ERROR] Failures: 
[ERROR]   TimeDifferenceTest.test:50->queryDataByJdbcDriver:89 
Expected: is <2025-02-25T01:17:34.640356152Z>
     but: was <2025-02-24T09:17:34.640356152Z>
[INFO] 
[ERROR] Tests run: 1, Failures: 1, Errors: 0, Skipped: 0

It is worth mentioning that https://github.com/linghengqian/influxdb-3-core-jdbc-test/blob/master/src/test/java/io/github/linghengqian/FlightSqlTest.java also does unit testing for the Arrow Flight Java API and does not have this problem.
For the time zone of Asia/Shanghai, the timestamp obtained by this unit test through the HTTP port is normal, but the timestamp obtained through the Arrow Flight JDBC Driver has a 16-hour error. This is unreasonable.
[JDBC] Timezone improvements for the JDBC driver #463 seems to suggest that use of java.time.Instant needs to be circumvented?

Is this different than #463/#464?

Is this different than #463/#464?

I just noticed that GH-463: Improve TZ support for JDBC driver #464 is still in draft status. And for [JDBC] Timezone improvements for the JDBC driver #463 , it doesn't mention how to handle java.time.Instant.
I would say I'm still a newbie to arrow. If I need to test the related PR, according to https://arrow.apache.org/docs/dev/developers/java/building.html , do I need to do this?

git clone git@github.com:aiguofer/arrow-java.git -b improved_tz_support
cd ./arrow/
mvn clean install
mvn generate-resources -Pgenerate-libs-cdata-all-os -N
mvn generate-resources -Pgenerate-libs-jni-macos-linux -N
mvn generate-resources -Pgenerate-libs-jni-windows -N
mvn -Darrow.c.jni.dist.dir=<absolute path to your arrow folder>/java-dist/lib -Parrow-c-data clean install
mvn \
    -Darrow.cpp.build.dir=<absolute path to your arrow folder>/java-dist/lib/ \
    -Darrow.c.jni.dist.dir=<absolute path to your arrow folder>/java-dist/lib/ \
    -Parrow-jni clean install

I will say I'm a little curious as to why the <absolute path to your arrow folder> is not fixed... Isn't the arrow git directory just the ${user.dir} property in Maven?

You don't need the JNI build, just the pure Java build (so a simple mvn install -DskipTests should be sufficient)

#463 lists Instant as part of java.time objects that need to be better supported. More test cases demonstrating the problem would be helpful to have there, I think.

That said, yes, the driver's handling of timestamps has long been questionable, it's just a matter of defining what's "right", especially for supporting the legacy java.sql datetime types (which of course is not what you're concerned about here but is part of the problem).

@lidavidm

@lidavidm I'm not sure if I should reply in the current issue or in GH-463: Improve TZ support for JDBC driver #464. But considering GH-463: Improve TZ support for JDBC driver #464 is still a draft, I tend to ask questions in the current issue.
There is apparently a formatting issue in GH-463: Improve TZ support for JDBC driver #464 which requires the -Dspotless.check.skip=true parameter.
I have tested this PR at linghengqian/influxdb-3-core-jdbc-test@8ae1aaa . I can confirm that this PR still has a bug. The java.time.Instant obtained from the query is 8 hours different from the original timestamp, which is still unreasonable.

sdk install java 21.0.6-ms
sdk use java 21.0.6-ms

git clone git@github.com:aiguofer/arrow-java.git -b improved_tz_support
cd ./arrow/
mvn clean install -DskipTests -Dspotless.check.skip=true
cd ../

git clone git@github.com:linghengqian/influxdb-3-core-jdbc-test.git
cd ./influxdb-3-core-jdbc-test/
sdk use java 21.0.6-ms
./mvnw -T 1C -Dtest=TimeDifferenceTest clean test

Click me to view the core logic of the unit test🥯🥨🍟🧂🥖🥚🍔🦪🍜🍘

$ ./mvnw -T 1C -Dtest=TimeDifferenceTest clean test
[INFO] Scanning for projects...
[INFO] 
[INFO] Using the MultiThreadedBuilder implementation with a thread count of 16
[INFO] 
[INFO] ----------< io.github.linghengqian:influxdb-3-core-jdbc-test >----------
[INFO] Building influxdb-3-core-jdbc-test 1.0-SNAPSHOT
[INFO]   from pom.xml
[INFO] --------------------------------[ jar ]---------------------------------
[INFO] 
[INFO] --- clean:3.2.0:clean (default-clean) @ influxdb-3-core-jdbc-test ---
[INFO] Deleting /home/linghengqian/TwinklingLiftWorks/git/public/influxdb-3-core-jdbc-test/target
[INFO] 
[INFO] --- resources:3.3.1:resources (default-resources) @ influxdb-3-core-jdbc-test ---
[INFO] skip non existing resourceDirectory /home/linghengqian/TwinklingLiftWorks/git/public/influxdb-3-core-jdbc-test/src/main/resources
[INFO] 
[INFO] --- compiler:3.13.0:compile (default-compile) @ influxdb-3-core-jdbc-test ---
[INFO] No sources to compile
[INFO] 
[INFO] --- resources:3.3.1:testResources (default-testResources) @ influxdb-3-core-jdbc-test ---
[INFO] skip non existing resourceDirectory /home/linghengqian/TwinklingLiftWorks/git/public/influxdb-3-core-jdbc-test/src/test/resources
[INFO] 
[INFO] --- compiler:3.13.0:testCompile (default-testCompile) @ influxdb-3-core-jdbc-test ---
[INFO] Recompiling the module because of changed source code.
[INFO] Compiling 7 source files with javac [debug target 21] to target/test-classes
[INFO] 
[INFO] --- surefire:3.5.2:test (default-test) @ influxdb-3-core-jdbc-test ---
[INFO] Using auto detected provider org.apache.maven.surefire.junitplatform.JUnitPlatformProvider
[INFO] 
[INFO] -------------------------------------------------------
[INFO]  T E S T S
[INFO] -------------------------------------------------------
[INFO] Running io.github.linghengqian.TimeDifferenceTest
SLF4J(W): No SLF4J providers were found.
SLF4J(W): Defaulting to no-operation (NOP) logger implementation
SLF4J(W): See https://www.slf4j.org/codes.html#noProviders for further details.
2月 25, 2025 11:11:36 上午 org.apache.arrow.driver.jdbc.shaded.org.apache.arrow.memory.BaseAllocator <clinit>
信息: Debug mode disabled. Enable with the VM option -Darrow.memory.debug.allocator=true.
2月 25, 2025 11:11:36 上午 org.apache.arrow.driver.jdbc.shaded.org.apache.arrow.memory.DefaultAllocationManagerOption getDefaultAllocationManagerFactory
信息: allocation manager type not specified, using netty as the default type
2月 25, 2025 11:11:36 上午 org.apache.arrow.driver.jdbc.shaded.org.apache.arrow.memory.CheckAllocator reportResult
信息: Using DefaultAllocationManager at memory/netty/DefaultAllocationManagerFactory.class
[ERROR] Tests run: 1, Failures: 1, Errors: 0, Skipped: 0, Time elapsed: 3.419 s <<< FAILURE! -- in io.github.linghengqian.TimeDifferenceTest
[ERROR] io.github.linghengqian.TimeDifferenceTest.test -- Time elapsed: 3.354 s <<< FAILURE!
java.lang.AssertionError: 

Expected: is <2025-02-25T03:11:24.614425710Z>
     but: was <2025-02-24T19:11:24.614425710Z>
        at org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:20)
        at org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:8)
        at io.github.linghengqian.TimeDifferenceTest.queryDataByJdbcDriver(TimeDifferenceTest.java:89)
        at io.github.linghengqian.TimeDifferenceTest.test(TimeDifferenceTest.java:50)
        at java.base/java.lang.reflect.Method.invoke(Method.java:580)
        at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
        at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)

[INFO] 
[INFO] Results:
[INFO] 
[ERROR] Failures: 
[ERROR]   TimeDifferenceTest.test:50->queryDataByJdbcDriver:89 
Expected: is <2025-02-25T03:11:24.614425710Z>
     but: was <2025-02-24T19:11:24.614425710Z>
[INFO] 
[ERROR] Tests run: 1, Failures: 1, Errors: 0, Skipped: 0
[INFO] 
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  4.894 s (Wall Clock)
[INFO] Finished at: 2025-02-25T11:11:38+08:00
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal org.apache.maven.plugins:maven-surefire-plugin:3.5.2:test (default-test) on project influxdb-3-core-jdbc-test: There are test failures.
[ERROR] 
[ERROR] See /home/linghengqian/TwinklingLiftWorks/git/public/influxdb-3-core-jdbc-test/target/surefire-reports for the individual test results.
[ERROR] See dump files (if any exist) [date].dump, [date]-jvmRun[N].dump and [date].dumpstream.
[ERROR] -> [Help 1]
[ERROR] 
[ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
[ERROR] Re-run Maven using the -X switch to enable full debug logging.
[ERROR] 
[ERROR] For more information about the errors and possible solutions, please read the following articles:
[ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoFailureException

Or should I close the current issue and reply on [JDBC] Timezone improvements for the JDBC driver #463? What I was referring to was exactly org.apache.arrow:flight-sql-jdbc-driver:19.0.0-SNAPSHOT.

It would help to keep these observations in one spot. It's okay to comment in the draft PR, especially since you just tested it.

It would help to keep these observations in one spot. It's okay to comment in the draft PR, especially since you just tested it.

No problem, let's continue on the GH-463: Improve TZ support for JDBC driver #464 side. I will close the current issue.

#463 was fixed by adding support for Instant et al, but I think the underlying correctness is still questionable, I have opened a new issue in #732 summarizing the observed behavior of PostgreSQL's JDBC driver. I agree that the Flight SQL driver is not handling things correctly.

Navigation Menu

Repository navigation

`java.time.Instant` obtained through Arrow Flight JDBC Driver is 16 hours different from the original timestamp #636

Describe the bug, including details regarding any error messages, version, and platform.

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

java.time.Instant obtained through Arrow Flight JDBC Driver is 16 hours different from the original timestamp #636

Description

Describe the bug, including details regarding any error messages, version, and platform.

Activity

lidavidm commented on Feb 25, 2025

linghengqian commented on Feb 25, 2025

lidavidm commented on Feb 25, 2025

lidavidm commented on Feb 25, 2025

lidavidm commented on Feb 25, 2025

linghengqian commented on Feb 25, 2025

lidavidm commented on Feb 25, 2025

linghengqian commented on Feb 25, 2025

lidavidm commented on Apr 28, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

`java.time.Instant` obtained through Arrow Flight JDBC Driver is 16 hours different from the original timestamp #636