[jdbc-v2] Incorrect timezone transformation when reading from ClickHouse

[GFriedrich]

Description

I've been using the JDBC driver 0.7.2 for quite a while and now decided to update to 0.9.0. Unfortunately I found that the update to the new driver broke the reading of timestamps. To be exact: Timestamps are read with an incorrect "time" value as the timezone is incorrectly translated to the clients timezone. This currently blocks me from using any version beyond 0.7.2 (and without applying any workarounds).

Steps to reproduce

1. Have a ClickHouse server running with UTC (default timezone settings of the driver are used)
2. Use a client that runs on a different timezone (e.g. GMT+01:00)
3. Write a timestamp to ClickHouse (e.g. "2025-07-01T01:00:00+01:00")
4. Check the timestamp inside the ClickHouse DB (which is correctly set to "2025-07-01T00:00:00Z")
5. Read the timestamp with the same setup
6. Find that the timestamp is now different (e.g. for this example "2025-07-01T00:00:00+01:00")

Expected Behaviour

Different timezones should not cause a different timestamp to be returned from the DB.

Code Example

I'm using Spring Data repositories to access my data, so the example for me is pretty minimal, which is essentially:

var writtenEntity = clickHouseRepository.save(entityToWrite)
val readEntity = clickHouseRepository.findById(writtenEntity.id)

The timestamps within those entities are now different and it used to work with 0.7.2 No other changes were made besides updating to the new driver version.

Potential location of the issue

I think the issue is at: https://github.com/ClickHouse/clickhouse-java/blob/d4792b7779a725eef3df5a15226ef52c4fc79a4f/jdbc-v2/src/main/java/com/clickhouse/jdbc/internal/JdbcUtils.java#L224 The value contains a ZonedDateTime with value 2025-07-01T00:00:00Z which is correctly read from the DB. Then this is transformed to a LocalDateTime so you get the timestamp without the timezone, so 2025-07-01T00:00:00. Now when reading things back to the Timestamp, the implementation will use the default Calendar of the machine, which is here a timezone of +01:00, so you end up with 2025-07-01T00:00:00+01:00, which is wrong. Of course I could set the default calendar to a UTC one, but this would cause other unwanted side effects for my application.

Beside this I also found that the getTimestamp implementation of the ResultSet to be wrong: https://github.com/ClickHouse/clickhouse-java/blob/d4792b7779a725eef3df5a15226ef52c4fc79a4f/jdbc-v2/src/main/java/com/clickhouse/jdbc/ResultSetImpl.java#L1107-L1126 Here again the zdt contains the value of 2025-07-01T00:00:00Z. This then gets manually translated to the calendar for +01:00 to c with 2025-07-01T00:00:00+01:00. The transition to the Timestamp afterwards doesn't change things and is then kept at 2025-07-01T00:00:00+01:00. For this part here I think this broke with https://github.com/ClickHouse/clickhouse-java/pull/2107 This is because the previous implementation used to use a UTC calendar and only the translation later on to a Timestamp would have added the default machine timezone, like: zdt with 2025-07-01T00:00:00Z becomes c with 2025-07-01T00:00:00+00:00 becomes Timestamp with 2025-07-01T01:00:00+01:00. (Of course I don't know if the introduction of the other calendar made sense in other places, but at least for reading the timestamp here it caused wrong results).

Configuration

Client Configuration

Nothing specific - defaults were used

Environment

• JDBC version: 0.9.0
• Language version: Java 21
• OS: Linux

ClickHouse Server

• ClickHouse Server version: 25.6
• ClickHouse Server non-default settings, if any: N/A
• CREATE TABLE statements for tables involved:

CREATE TABLE IF NOT EXISTS my_db.my_table
(
    id             UUID,
    other_id       Int32,
    payload        String,
    timestamp      DateTime64(3, 'UTC')
    ) ENGINE = ReplacingMergeTree()
ORDER BY other_id;

[chernser]

Good day, @GFriedrich ! Thank you for reporting the issue!

I think you are right. I will look into it.

[chernser]

Triage: we need to fix handling date-time read from DB and avoid extra conversions. Data in DB should be converted to a local date time, currently only timezone is set to local but time/date part is in UTC.