ClickHouse / ClickHouse/clickhouse-java
[jdbc-v2] setObject(LocalDateTime) applies the JVM timezone and stores a shifted value
- Dominant language
- Java
- Stars
- 1.6k
- Forks
- 636
- Avg merge
- 2d 16h
- Merged PRs (30d)
- 28
Description
## Description
JDBC V2 interprets a `LocalDateTime` passed to `PreparedStatement.setObject()` using the JVM default timezone.
This happens before the value is sent to ClickHouse. Consequently, the persisted Unix timestamp depends on the timezone of the JVM running the application.
A `LocalDateTime` does not represent an absolute instant. It contains only local date and time fields. When it is written to a ClickHouse `DateTime` or `DateTime64` column, those fields should be interpreted in the timezone declared by the target column, or in the ClickHouse server timezone when the column has no explicit timezone. The JVM default timezone should not participate in this conversion.
For example:
```text
LocalDateTime: 2019-03-18 10:01:17.123456
JVM timezone: America/Bahia_Banderas (UTC-06:00)
Target column timezone: UTC
```
The expected interpretation is:
```text
2019-03-18 10:01:17.123456 UTC
```
JDBC V2 instead interprets it as:
```text
2019-03-18 10:01:17.123456 America/Bahia_Banderas
```
and sends the corresponding instant:
```text
2019-03-18 16:01:17.123456 UTC
```
ClickHouse therefore receives and correctly stores an already shifted Unix timestamp.
This was originally found while migrating the Trino ClickHouse connector from the legacy JDBC implementation (`0.7.1-patch1`) to JDBC V2 (`0.10.0`), but the reproducer below uses the JDBC driver directly.
### Steps to reproduce
1. Run ClickHouse with the server timezone set to `UTC`.
2. Set the JVM default timezone to `America/Bahia_Banderas`.
3. Create a `DateTime64(6, 'UTC')` column.
4. Insert a `LocalDateTime` using `PreparedStatement.setObject()`.
5. Query the persisted epoch using `toUnixTimestamp64Micro()`.
### Actual Behaviour
No exception is thrown, but the prepared-statement value is shifted by the JVM timezone offset:
```text
JVM timezone: America/Bahia_Banderas
Server timezone: UTC
SQL literal:
expected=1552903277123456
actual=1552903277123456
setObject(LocalDateTime):
expected=1552903277123456
actual=1552924877123456
difference=21600000000 microseconds
```
The difference is six hours, matching the JVM timezone offset on the tested date.
Changing only the JVM default timezone to `UTC` makes the same code produce the expected result.
### Expected Behaviour
The local date and time fields of `LocalDateTime` should be interpreted in the timezone of the target ClickHouse column.
For example:
| Target column | LocalDateTime | Expected stored instant |
|---|---|---|
| `DateTime64(6, 'UTC')` | `2019-03-18 10:01:17.123456` | `2019-03-18 10:01:17.123456 UTC` |
| `DateTime64(6, 'Europe/Moscow')` | `2019-03-18 10:01:17.123456` | `2019-03-18 07:01:17.123456 UTC` |
In both cases, selecting the value using the column timezone should produce the original local fields:
```text
2019-03-18 10:01:17.123456
```
For a `DateTime` or `DateTime64` column without an explicit timezone, the server timezone should be used.
The result must not change when the same application is executed with a different JVM default timezone.
If the JDBC driver does not know the target column timezone, it can preserve the `LocalDateTime` fields as a textual timestamp and let ClickHouse apply the target column or server timezone.
### Code Example
```java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.util.Properties;
import java.util.TimeZone;
public final class ClickHouseLocalDateTimeWriteReproducer
{
private static final LocalDateTime VALUE =
LocalDateTime.of(2019, 3, 18, 10, 1, 17, 123_456_000);
private ClickHouseLocalDateTimeWriteReproducer() {}
public static void main(String[] args)
throws Exception
{
TimeZone originalTimeZone = TimeZone.getDefault();
TimeZone.setDefault(TimeZone.getTimeZone("America/Bahia_Banderas"));
try {
Properties properties = new Properties();
properties.setProperty("user", "default");
properties.setProperty("password", "");
try (Connection connection = DriverManager.getConnection(
"jdbc:clickhouse://localhost:8123/default",
properties);
Statement statement = connection.createStatement()) {
System.out.println("JVM timezone: " + TimeZone.getDefault().getID());
try (ResultSet result = statement.executeQuery("SELECT timezone()")) {
result.next();
System.out.println("Server timezone: " + result.getString(1));
}
statement.execute("DROP TABLE IF EXISTS jdbc_v2_local_datetime_repro");
statement.execute("""
CREATE TABLE jdbc_v2_local_datetime_repro
(
id UInt8,
value DateTime64(6, 'UTC')
)
ENGINE = Memory
""");
// Control row: ClickHouse interprets these local fields using the target column timezone.
statement.execute("""
INSERT INTO jdbc_v2_local_datetime_repro
VALUES (1, '2019-03-18 10:01:17.123456')
""");
// Problematic row: JDBC V2 first interprets LocalDateTime using the JVM default timezone.
try (PreparedStatement preparedStatement = connection.prepareStatement(
"INSERT INTO jdbc_v2_local_datetime_repro VALUES (?, ?)")) {
preparedStatement.setInt(1, 2);
preparedStatement.setObject(2, VALUE);
preparedStatement.executeUpdate();
}
long expected =
VALUE.toEpochSecond(ZoneOffset.UTC) * 1_000_000L
+ VALUE.getNano() / 1_000;
try (ResultSet result = statement.executeQuery("""
SELECT id, toUnixTimestamp64Micro(value)
FROM jdbc_v2_local_datetime_repro
ORDER BY id
""")) {
while (result.next()) {
int id = result.getInt(1);
long actual = result.getLong(2);
System.out.printf(
"id=%d expected=%d actual=%d difference=%d%n",
id,
expected,
actual,
actual - expected);
}
}
}
}
finally {
TimeZone.setDefault(originalTimeZone);
}
}
}
```
### Workaround
Passing the same local date and time as a string preserves the expected fields:
```java
preparedStatement.setString(
parameterIndex,
"2019-03-18 10:01:17.123456");
```
ClickHouse then interprets the string using the timezone of the target column.
`ZonedDateTime` is not an equivalent workaround for this use case: `ZonedDateTime` represents an absolute instant with timezone information, while the original value has SQL `TIMESTAMP WITHOUT TIME ZONE` semantics.
### Suspected Cause
JDBC V2 converts `LocalDateTime` to a Unix timestamp using the connection's default calendar:
https://github.com/ClickHouse/clickhouse-java/blob/v0.10.0/jdbc-v2/src/main/java/com/clickhouse/jdbc/PreparedStatementImpl.java#L701-L710
```java
else if (x instanceof LocalDateTime) {
return "fromUnixTimestamp64Nano(" +
DataTypeUtils.toUnixTimestampString(
(LocalDateTime) x,
defaultCalendar.getTimeZone()) +
")";
}
```
The connection initializes this calendar from the JVM default timezone:
https://github.com/ClickHouse/clickhouse-java/blob/v0.10.0/jdbc-v2/src/main/java/com/clickhouse/jdbc/ConnectionImpl.java#L107-L111
```java
this.defaultCalendar = Calendar.getInstance();
```
Therefore, the conversion currently behaves approximately as:
```java
localDateTime
.atZone(ZoneId.systemDefault())
.toInstant();
```
The timezone of the target ClickHouse column is not used.
### Configuration
#### Client Configuration
```java
Properties properties = new Properties();
properties.setProperty("user", "default");
properties.setProperty("password", "");
Connection connection = DriverManager.getConnection(
"jdbc:clickhouse://localhost:8123/default",
properties);
```
The following properties did not change the result:
```text
use_server_time_zone=false
use_time_zone=UTC
```
#### Environment
- Client version: `clickhouse-jdbc 0.10.0`
- JDBC implementation: V2 (`com.clickhouse.jdbc.Driver`)
- Language version: Java `23.0.2`
- OS: Linux
- JVM timezone: `America/Bahia_Banderas`
#### ClickHouse Server
- ClickHouse Server version: `24.12.1.1614`
- ClickHouse Server timezone: `UTC`
- ClickHouse Server non-default settings, if any: none relevant
- `CREATE TABLE` statement:
```sql
CREATE TABLE jdbc_v2_local_datetime_repro
(
id UInt8,
value DateTime64(6, 'UTC')
)
ENGINE = Memory;
```
Contributor guide
Assessment
This issue has not been assessed yet.