[jdbc-v2] setObject(LocalDateTime) applies the JVM timezone and stores a shifted value

[Kvel4]

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:

LocalDateTime:          2019-03-18 10:01:17.123456
JVM timezone:           America/Bahia_Banderas (UTC-06:00)
Target column timezone: UTC

The expected interpretation is:

2019-03-18 10:01:17.123456 UTC

JDBC V2 instead interprets it as:

2019-03-18 10:01:17.123456 America/Bahia_Banderas

and sends the corresponding instant:

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:

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:

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

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:

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

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

this.defaultCalendar = Calendar.getInstance();

Therefore, the conversion currently behaves approximately as:

localDateTime
        .atZone(ZoneId.systemDefault())
        .toInstant();

The timezone of the target ClickHouse column is not used.

Configuration

Client Configuration

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:

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:

CREATE TABLE jdbc_v2_local_datetime_repro
(
    id UInt8,
    value DateTime64(6, 'UTC')
)
ENGINE = Memory;

[chernser]

@Kvel4

Thank you for reporting this!

I will try to respond asap.