clickhouse-client parses Array(DateTime) as LocalDateTime[] without time zone

[misha-nik]

Problem:

ClickHouse doc says DateTime is an instant in time. But in actual client's response Array(DateTime) represents as LocalDateTime[] (or more accurete ClickHouseArrayValue<LocalDateTime>) so without additional information it is imposible to convert it in collection of java.time.Instant. At the same time simple DateTime value represents as ClickHouseDateTimeValue which stores TimeZone and correctly converts via asInstant()

So my question is: Is there a way to get Array(DateTime) as Instant[]?

For now I can see 2 workarounds:

1. Share ClickHouseConfig with use_time_zone: ... and use LocalDateTime::atZone(...), but it does not solve problem when use_server_time_zone: true
2. Rewrite queries with arrayMap(toUnixTimestamp, ...)

Example for clearness:

Here I requests same DateTime using clients with different settings With getInstantExample() is everything OK But how to get timestamp in getInstantArrayExample()?

import java.time.Instant;
import java.time.LocalDateTime;
import java.util.Arrays;
import java.util.Map;

import com.clickhouse.client.ClickHouseClient;
import com.clickhouse.client.ClickHouseConfig;
import com.clickhouse.client.ClickHouseException;
import com.clickhouse.client.ClickHouseFormat;
import com.clickhouse.client.ClickHouseNodes;
import com.clickhouse.client.ClickHouseResponse;
import com.clickhouse.client.ClickHouseValue;
import com.clickhouse.client.config.ClickHouseClientOption;

class CHClientTz {
    public static void main(String[] args) throws ClickHouseException {
        getInstantExample();
        getInstantArrayExample();
    }


    private static void getInstantExample() throws ClickHouseException {
        ClickHouseNodes server = ClickHouseNodes.of("http://localhost:8123");
        LocalDateTime dateTimeBelgrade;
        Instant instantBelgrade;
        try (ClickHouseClient client = createBelgradeTzClient()) {
            ClickHouseValue chValue = requestTimestampValue(server, client);
            dateTimeBelgrade = chValue.asObject(LocalDateTime.class);
            instantBelgrade = chValue.asInstant();
        }
        // 2023-01-01T18:00
        System.out.println(dateTimeBelgrade);

        LocalDateTime dateTimeLondon;
        Instant instantLondon;
        try (ClickHouseClient client = createLondonTzClient()) {
            ClickHouseValue chValue = requestTimestampValue(server, client);
            dateTimeLondon = chValue.asObject(LocalDateTime.class);
            instantLondon = chValue.asInstant();
        }
        // 2023-01-01T17:00
        System.out.println(dateTimeLondon);


        assert !dateTimeLondon.equals(dateTimeBelgrade); // tz diff 1h
        assert instantLondon.equals(instantBelgrade); // same instant
    }

    private static void getInstantArrayExample() throws ClickHouseException {
        ClickHouseNodes server = ClickHouseNodes.of("http://localhost:8123");
        LocalDateTime[] dateTimeBelgrade;
        try (ClickHouseClient client = createBelgradeTzClient()) {
            ClickHouseValue chValue = requestTimestampArrayValue(server, client);
//            debug info:
//            chValue = {ClickHouseArrayValue@2642} "ClickHouseArrayValue[[2023-01-01T18:00]]"
//              > value = {LocalDateTime[1]@3120}
            dateTimeBelgrade = chValue.asArray(LocalDateTime.class);
        }
        // [2023-06-01T18:00]
        System.out.println(Arrays.toString(dateTimeBelgrade));

        LocalDateTime[] dateTimeLondon;
        try (ClickHouseClient client = createLondonTzClient()) {
            ClickHouseValue chValue = requestTimestampArrayValue(server, client);
            dateTimeLondon = chValue.asArray(LocalDateTime.class);
        }
        // [2023-01-01T17:00]
        System.out.println(Arrays.toString(dateTimeLondon));

        assert !dateTimeLondon[0].equals(dateTimeBelgrade[0]); // tz diff 1h
//        how to get actual timestamp?
    }


    private static ClickHouseClient createLondonTzClient() {
        ClickHouseConfig config = new ClickHouseConfig(Map.of(
                ClickHouseClientOption.USE_SERVER_TIME_ZONE, false,
                ClickHouseClientOption.USE_TIME_ZONE, "Europe/London"
        ), null, null, null);
        return ClickHouseClient.builder().config(config).build();
    }

    private static ClickHouseClient createBelgradeTzClient() {
        ClickHouseConfig config = new ClickHouseConfig(Map.of(
                ClickHouseClientOption.USE_SERVER_TIME_ZONE, false,
                ClickHouseClientOption.USE_TIME_ZONE, "Europe/Belgrade"
        ), null, null, null);
        return ClickHouseClient.builder().config(config).build();
    }

    private static ClickHouseValue requestTimestampValue(ClickHouseNodes server, ClickHouseClient client) throws ClickHouseException {
        try (ClickHouseResponse response = client.connect(server)
                .format(ClickHouseFormat.RowBinaryWithNamesAndTypes)
                .query("SELECT toDateTime('2023-01-01 17:00:00')")
                .executeAndWait()) {
            return response.firstRecord().getValue(0);
        }
    }

    private static ClickHouseValue requestTimestampArrayValue(ClickHouseNodes server, ClickHouseClient client) throws ClickHouseException {
        try (ClickHouseResponse response = client.connect(server)
                .format(ClickHouseFormat.RowBinaryWithNamesAndTypes)
                .query("SELECT [toDateTime('2023-01-01 17:00:00')]")
                .executeAndWait()) {
            return response.firstRecord().getValue(0);
        }
    }


}

Configuration

Environment

• Client version: clickhouse-client-0.3.2-patch10
• Language version: Java 17
• OS: Mac OS Monterey

ClickHouse server

• ClickHouse Server version: 23.3.1.2823
• Empty DB runing in docker

[zhicwu]

Sorry for the late reply. Yes, Instant sounds better but let me see if I can tackle this one together with #1270.

[misha-nik]

Thank you for reponce. Looking forward for this improvement!