DateTime('Fixed/UTC±HH:MM:SS') timezone silently dropped — wall-clock shifted to UTC

[claude]

Description

ClickHouse server supports declaring DateTime/DateTime64 columns with a synthetic fixed-offset timezone name of the form Fixed/UTC±HH:MM:SS (e.g. DateTime('Fixed/UTC+05:30:00')). The server emits these names verbatim in the column type metadata returned with results (both TSV/HTTP and RowBinaryWithNamesAndTypes).

The Java client resolves the column timezone via java.util.TimeZone.getTimeZone(name):

• clickhouse-data/src/main/java/com/clickhouse/data/ClickHouseColumn.java:207, :215, :224, :235 — column.timeZone = TimeZone.getTimeZone(column.parameters.get(...).replace("'", ""))

Fixed/UTC+05:30:00 is not a recognized JDK zone ID, and it does not match the only custom-ID form TimeZone.getTimeZone understands (GMT±HH:MM). Per its documented contract, TimeZone.getTimeZone silently returns the GMT zone for any unrecognized ID — no exception. So column.getTimeZone() becomes GMT/UTC instead of +05:30.

When a row is read, the v2 binary reader does:

• client-v2/src/main/java/com/clickhouse/client/api/data_formats/internal/BinaryStreamReader.java:1053 — Instant.ofEpochSecond(time).atZone(tz.toZoneId()) (DateTime32)
• client-v2/.../BinaryStreamReader.java:1100 — same for DateTime64

with tz coming from actualColumn.getTimeZoneOrDefault(timeZone) (BinaryStreamReader.java:118). Because tz silently degraded to UTC, the unix instant on the wire (which is read correctly) is rendered in UTC wall-clock rather than the column's declared +05:30 offset. The returned ZonedDateTime/LocalDateTime is therefore 5h 30m off from the wall-clock value the server prints for that column. No exception is raised; the value is silently wrong.

For comparison, an IANA timezone like DateTime('Asia/Kolkata') (also UTC+05:30) resolves correctly via TimeZone.getTimeZone, so atZone(tz.toZoneId()) yields the right wall-clock. Two columns that should display the same wall-clock value diverge purely on whether the tz name happens to be IANA.

This is the Java surface of the same root issue reported for clickhouse-cs (.NET): ClickHouse/clickhouse-cs#370. In .NET NodaTime's GetZoneOrNull returns null → UTC fallback; in Java TimeZone.getTimeZone returns GMT → UTC fallback. Same silent shift.

Note: this is distinct from #2787 (jdbc-v2 getTimestamp ignoring the column tz and using the JVM default) — that bug is downstream in the JDBC Timestamp conversion, whereas this one is the upstream failure to resolve the synthetic Fixed/UTC±HH:MM:SS name into the correct offset.

ClickHouse server version

26.5.1.882. Confirmed the server emits the synthetic name in column metadata:

$ curl -s "http://localhost:8123/?query=SELECT+toDateTime('2024-01-15+10:30:00',+'Fixed/UTC%2B05:30:00')+AS+d+FORMAT+TSVWithNamesAndTypes"
d
DateTime('Fixed/UTC+05:30:00')
2024-01-15 10:30:00

The bug itself was diagnosed by code analysis plus a standalone check of TimeZone.getTimeZone behavior; no end-to-end client test was executed against the server.

Reproduction

Unit-level reproduction of the resolution failure (the load-bearing step), independent of a running server:

import java.util.TimeZone;
import java.time.Instant;
import org.testng.annotations.Test;
import static org.testng.Assert.assertEquals;

public class FixedUtcTzTest {
    @Test
    public void fixedUtcOffsetNameResolves() {
        // Names produced by the server in column type metadata.
        TimeZone fixed   = TimeZone.getTimeZone("Fixed/UTC+05:30:00");
        TimeZone kolkata = TimeZone.getTimeZone("Asia/Kolkata"); // IANA, also +05:30

        // unix instant for wall-clock 2024-01-15 10:30:00 at +05:30
        long epoch = Instant.parse("2024-01-15T05:00:00Z").getEpochSecond();

        // EXPECTED: both render the same wall-clock 2024-01-15T10:30
        // ACTUAL:   Fixed/UTC+05:30:00 silently degraded to GMT, renders 2024-01-15T05:00
        assertEquals(Instant.ofEpochSecond(epoch).atZone(fixed.toZoneId()).toLocalDateTime().toString(),
                     Instant.ofEpochSecond(epoch).atZone(kolkata.toZoneId()).toLocalDateTime().toString());
        // fails: "2024-01-15T05:00" != "2024-01-15T10:30"
    }
}

End-to-end via client-v2, the same divergence appears when reading the two columns back:

try (Client client = new Client.Builder().addEndpoint("http://localhost:8123")
        .setUsername("default").setPassword("").build()) {
    // returns 2024-01-15T05:00 (UTC), should be 2024-01-15T10:30 (+05:30)
    var r1 = client.query("SELECT toDateTime('2024-01-15 10:30:00','Fixed/UTC+05:30:00') AS d").get();
    // returns 2024-01-15T10:30 correctly
    var r2 = client.query("SELECT toDateTime('2024-01-15 10:30:00','Asia/Kolkata') AS d").get();
}

The same shape applies to DateTime64(p, 'Fixed/UTC±HH:MM:SS').

Suggested fix

At the four TimeZone.getTimeZone(...) call sites in ClickHouseColumn.java (:207, :215, :224, :235), detect names of the form Fixed/UTC±HH:MM[:SS] and build a fixed-offset zone explicitly (e.g. ZoneOffset.ofHoursMinutesSeconds(...) / TimeZone.getTimeZone(ZoneOffset...)) when the standard lookup would otherwise fall back to GMT. Centralizing the resolution in a small helper would keep the four sites consistent. The symmetric write path (SerializerUtils.java:374,379 writes getTimeZoneOrDefault(...).getID()) should also round-trip such offsets correctly.

Link

Relayed from clickhouse-cs: ClickHouse/clickhouse-cs#370