Repository navigation
V2 driver ignores column timezones when reading via getTimestamp #2787
Description
Description
Steps to reproduce
When running the program below (see Code Example) I get the following output
Table test1 created.
Original Timestamp: 2026-03-12 22:58:08.000 (millis: 1773356288000)
Record inserted.
Read t_tokyo: 2026-03-13 07:58:08.0 (millis: 1773388688000)
Read t_ny: 2026-03-12 18:58:08.0 (millis: 1773341888000)
Note that one Timestamp value is inserted for both columns (as number of millis since epoch so it should be timezone independent) but when read back 2 different values are returned that are not equal to the original one.
I believe the issue is in
that completely ignores the column's timezone (zdt.getTimeZone()) and uses JVM default timezone to convert LocalDateTime value to java.sql.Timestamp.
Expected Behaviour
I expect V2 driver to behave the same way as V1 driver and correctly produce 3 identical timestamps:
Table test1 created.
Original Timestamp: 2026-03-12 23:02:25.000 (millis: 1773356545000)
Record inserted.
Read t_tokyo: 2026-03-12 23:02:25.0 (millis: 1773356545000)
Read t_ny: 2026-03-12 23:02:25.0 (millis: 1773356545000)
Code Example
package com.example;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Properties;
public class App {
public static void main(String[] args) {
String url = "jdbc:clickhouse://localhost:8123/default";
Properties props = new Properties();
props.setProperty("user", "default");
props.setProperty("password", "");
try (Connection conn = DriverManager.getConnection(url, props)) {
try (Statement stmt = conn.createStatement()) {
// 1. Create the table
stmt.execute("DROP TABLE IF EXISTS test1");
stmt.execute("CREATE TABLE test1 (" +
" t_tokyo DateTime(3, 'Asia/Tokyo'), " +
" t_ny DateTime(3, 'America/New_York') " +
") ENGINE = MergeTree ORDER BY t_tokyo");
System.out.println("Table test1 created.");
}
// 2. Insert record
Timestamp originalTimestamp = new Timestamp(System.currentTimeMillis());
System.out.println("Original Timestamp: " + originalTimestamp + " (millis: " + originalTimestamp.getTime() + ")");
String insertSql = "INSERT INTO test1 (t_tokyo, t_ny) VALUES (?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(insertSql)) {
pstmt.setTimestamp(1, originalTimestamp);
pstmt.setTimestamp(2, originalTimestamp);
pstmt.executeUpdate();
System.out.println("Record inserted.");
}
// 3. Read back
String selectSql = "SELECT t_tokyo, t_ny FROM test1";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(selectSql)) {
if (rs.next()) {
Timestamp readTokyo = rs.getTimestamp("t_tokyo");
Timestamp readNy = rs.getTimestamp("t_ny");
System.out.println("Read t_tokyo: " + readTokyo + " (millis: " + readTokyo.getTime() + ")");
System.out.println("Read t_ny: " + readNy + " (millis: " + readNy.getTime() + ")");
} else {
System.out.println("No record found.");
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
Environment
- Client version: 0.9.7
- Language version: 25
- OS: Linux
ClickHouse Server
- ClickHouse Server version: 26.2.3.2
- ClickHouse Server non-default settings, if any:
Reacted by Aleydin Karaimin, Maxim Savenko and Georg Friedrich
Activity
karaimin commentedon Mar 16, 2026
+1,
also hitting this
My setup includes a column:
occurred_at DateTime64(3, 'UTC'),The UTC timestamp gets properly inserted in UTC format, but on query fetching I am receiving
java.sql.Timestampobject from the driver.it contains
cdate="2024-01-15T12:00:00.000+0200"(notice the offset pointing to my current timezone)... and on later call of
ts.toInstant().toString()will output2024-01-15T10:00:00.000chernser commentedon Mar 16, 2026
Good day, @kostya-sh , @karaimin !
This is a common issue with timestamp. In your example you should also pass calendar for column timezone otherwise driver treats it as local time. Keep in mind driver may not known target data type.
We have documented most of this usecases in https://clickhouse.com/docs/integrations/language-clients/java/jdbc_date_time_guide .
I'd recommend using ZonedDateTime because it carry all the information.
karaimin commentedon Mar 17, 2026
@chernser
Thank for the fast reply.
My setup includes also Jooq as a wrapper to the JDBC ... so I don't have this control. It is strange because in V1 it works.
I am able to hack it on V2 by:
record.get(fieldName, LocalDateTime.class).toInstant(ZoneOffset.UTC)... but does not look convenientchernser commentedon Mar 17, 2026
@karaimin
Thank you for sharing your usecase!
I will look into how it works in jooq.
Do you write data using JDBC?
Would you please give a sample code how it is done from write to read?
Thanks!
kostya-sh commentedon Mar 19, 2026
I still think that this is a bug that should be fixed. When getting a timesamp from a column the user code generally doesn't know its timezone and sometimes cannot pass Calendar instance to getTimestamp method if it is called by some framework, etc.
The docs for [getTimstamp] (https://docs.oracle.com/en/java/javase/17/docs/api/java.sql/java/sql/ResultSet.html#getTimestamp(int,java.util.Calendar)) that takes a Calendar say:
Clickhouse however stores the column's timezone, just V2 driver doesn't use it (unlike V1 that works correctly).
chernser commentedon Apr 15, 2026
@kostya-sh
I will work on it. Sorry for the delayed response.
shashank-gb commentedon May 4, 2026
@karaimin @kostya-sh @chernser
I tried the same code mentioned above using different JDBC versions:
It looks like reading timestamps works consistently, but there is a difference during insertion between the two versions.
Observation with 0.7.2
Insertion
Data in ClickHouse,
Observation in 0.9.8
Insertion
Data In ClickHouse,
Summary
With 0.7.2 (client-v1): both columns end up with the same stored value
With 0.9.8 (client-v2): timestamps are shifted differently per timezone column
The discrepancy appears during insertion, not reading
chernser commentedon May 4, 2026
@karaimin @kostya-sh @shashank-gb
Thank you for waiting!
TL;DR
Timestamp value do not have timezone information and should be always read/write with specific calendar to get consistent result. It is better to use
LocalDateTimeandZonedDateTimeif possible because they tell about their timezone. In the example timestamp is written as UTC timestamp without convert and read in column timezone values. V2 has simplified logic in this part to make it more easy to understand and debug. V1 has many conversions that depend on server and client configuration.Explanation
Root cause of the problem is
Timestamp- it can be created in different timezones but has no timezone information. That is why it is important to use specific calendar insetTimestamp()when timezone can mismatch at any point.ClickHouse doesn't store a timezone in a column value. Specified timezone names are used only for text representation (please see https://clickhouse.com/docs/sql-reference/data-types/datetime64). However when we read data in RowBinary format we get timezone in type metadata and use it to construct ZonedDateTime value internally. When column has a timezone then it always the same otherwise server or session timezone is used. This last thing affects V1 version because it applies configured timezone to ALL columns. V2 follows JDBC spec and applies only specified calendar. We made it to allow reading values with specific timezone.
I've re-created the code (https://github.com/chernser/jdbc-drivers-playground/pull/1) and run (my timezone is "America/Los_Angeles"). For V1 I'm getting very different results because of many transformations:
In V2 if I specify same calendar on write and read it returns expected result (see
id: 2that was written with column timezone calendars):kostya-sh commentedon May 12, 2026
chernser commentedon May 12, 2026
@kostya-sh
the
Timestamphas no timezone information but they should be created with correct calendar to represent same date. If you use calendar for write and read with V2 it will work.But we do transformation on read because server tells what timezone to use and we doing only this transformation. V1 takes into account server and configured timezone what makes more complex transformation.
In other words - V2 does simpler transformation so application developer can easily compensate it. V1 transformation depend on more parameters and it is harder to fix.
kostya-sh commentedon May 13, 2026
chernser commentedon May 26, 2026
@kostya-sh
The problem with Timestamp it doesn't have information about timezone so application should take it from somewhere. Why ZonedDateTime was added and recommended.
When DateTime is returned by server it tells timezone in the column type in schema that comes as first row (we use RowBinaryWitihNamesAndTypes). If timezone is not set by user then server doesn't pass it and client should "guess". By default it comes from timezone header from a response but can be configured by client. This is where your are correct but it happens before.
True - passing calendar is problematic but this is why ZonedDateTime is recommended (https://clickhouse.com/docs/integrations/language-clients/java/jdbc_date_time_guide#java-and-jdbc-timestamp-apis). Current JDBC and client implementation support it.
5 remaining items
GFriedrich commentedon Oct 1, 2026
Hey @chernser,
I would like to add here that I'm still struggling with the same issue.
After raising this problem already a while ago at #2496 I was finally able to check on the latest ClickHouse driver (0.10.0) and found that I still have the same issue: The timezones are broken (as already mentioned in this thread here).
As my columns are just storing
DateTime64(3, 'UTC')I was able to add a little workaround to fix my problem:I've configured my connection with:
jdbc_type_mappingswith the value ofDateTime=java.time.Instant,DateTime32=java.time.Instant,DateTime64=java.time.InstantThis avoids the incorrect conversion to a local timestamp and returns the correct values for me as
Instantobject.Maybe this helps someone else as well.
In general though I agree with what was said here already: I think that the current behavior violates the JDBC driver contract and should be handled in a better way.
chernser commentedon Oct 1, 2026
Good day, @GFriedrich!
Thank you for bringing this up!
Sorry that the issue still not resolved.
Your workaround is one of better way to work with timestamps what JDBC API provides.
I do recommend using new time framework in Java -
java.time.Instantfor example. JDBC hasgetTimestamp(Calendar)and this method requires as to reinterpret what we read from database in calendar timezone.ClickHouse stores all timestamps without timezone (column definition is used to represent timestamp) so we use
ZonedDateTimeto preserve both parameters. Converting toInstantlike you do is the right way.If we start converting timezones it will require user to learn all underlying conversions event they do not need it and can do on application side. That is why I've created this guide https://clickhouse.com/docs/integrations/language-clients/java/date-time-guide.
I wish I could support all usecases out of the box but in context of JDBC it is quite hard - there are too many visions on the issue.
So help me, please, understand why current JDBC do not work for your case:
java.time.*or is there another problem linked to it?Thanks!
chernser commentedon Oct 1, 2026
Good day, @kostya-sh!
Sorry for the delayed response.
Would you please explain in details:
Thanks!
GFriedrich commentedon Oct 1, 2026
Thanks for getting back at this @chernser
So I'm using ClickHouse in combination with Spring Data JDBC. It uses under the hood the callback to ResultSets
getObjectmethod to prefill the response object before returning. So I can't trigger anything else on the original ClickHouse driver. I need to rely on what Spring Data is doing.The thing for me is the following scenario:
DateTime64(3, 'UTC')I personally would expect that this works without me doing any additional configurations on the driver and the current solution I found rather feels like a workaround than a proper solution.
I don't really mind what actual time object the driver returns, but what is important to me is, that the point in time the object refers to is the very same which it is not right now with the default setup.
Hope that all makes somewhat sense.
And thanks again for checking. 🙏
chernser commentedon Oct 1, 2026
@GFriedrich
Very much appreciate your explanation!
Getting ZonedDateTime with local timezone - this is something strange.
I need a bit more information to make sure I can reproduce it and fix:
ZonedDateTime- how to you work with the value?thanks!
GFriedrich commentedon Oct 1, 2026
Ok, so first of all I need to apologize. I've written down a scenario that was one of my test cases, but not the real production one. I confused myself while finding a solution and erroneously reverted to a test case instead of the original implementation.
The current implementation works like this:
java.sql.Timestamp(not a ZonedDateTime - sorry again for the confusion)((java.sql.Timestamp) returnedValue).toInstant()chernser commentedon Oct 1, 2026
@GFriedrich
no need to apologize.
we value this kind of issue the most because they help us understand the problem and solve it. Thank you for sharing with us it!
I will evaluate your case more carefully later next week.
Currently I see that the problem is in
java.sql.Timestampwhat is default object to return fromgetObject()and your workaround is actually a correct usage of JDBC.I see another code in some BI tools:
This code makes code more stable because doesn't rely on hidden JDBC logic or assumption. Setting a single mapping for whole application may not work for different parts of code expecting different types. Drive may not set another defaults because JDBC standard tells what to return by default (we've tried but got a few issue about default type of mismatch).
Returning to your case:
Would it work for you?
Thanks!
GFriedrich commentedon Oct 1, 2026
Unfortunately I can't do that because Spring Data JDBC uses a
CachedRowSetImplin between the driver and the Spring Data row set.The
CachedRowSetImpldoesn't have this method implemented. And theCachedRowSetImplprefills the data inside the cache by callinggetObject(...)without me being able to interfere with it.chernser commentedon Oct 1, 2026
@GFriedrich
I see the problem. :-(
I will try to look into it asap.
chernser commentedon Oct 30, 2026
@GFriedrich
I've found another problem with RowSet approach and have created this documentation https://github.com/ClickHouse/clickhouse-java/pull/3276/changes
Would you please review?
Thanks!
GFriedrich commentedon Oct 30, 2026
hey @chernser,
generally the PR looks good to me - just wondering what the "other problem" is you've mentioned.
Isn't this "just" the very same problem I've described above. 🤔 Or what am I missing?
chernser commentedon Oct 30, 2026
@GFriedrich
other problem is that RowSet API variant reads whole result set before returning - this might be overhead.
For example, when application builds output and copies data to data object then data is really get copied from client internal row buffer to cached result set then to data object. We will work to make this path shorter but current implementation expects that data is read from result set directly to application objects.
GFriedrich commentedon Oct 30, 2026
Ah ok. I thought that this was already clear when I said:
In general I think it would be still great to have a better working
getTimestamp(...)implementation. But it is at least great to see some documentation around this for Spring users. 🙏chernser commentedon Oct 30, 2026
@GFriedrich
for timestamps there is
org.springframework.jdbc.support.rowset.SqlRowSet#getTimestamp(java.lang.String, java.util.Calendar)but it may be not what you want as you have to know what fields should use this method.I understand that ideally it would be great to adjust client logic and we will review this path.
Much appreciate your help with the issue!