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

[linghengqian]

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?

[lidavidm]

Is this different than #463/#464?

[linghengqian]

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?

[lidavidm]

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

[lidavidm]

#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.

[lidavidm]

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).

[linghengqian]

• @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.

[lidavidm]

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.

[linghengqian]

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.

[lidavidm]

#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.