Query timezone depends on format

[misha-nik]

Describe the bug

I do 2 almost equals query SELECT toDateTime('2019-01-01 17:00:00') within same client with only difference in format: RowBinaryWithNamesAndTypes or TabSeparatedWithNamesAndTypes. Unexpectedly they returns two different instants in time.

It looks like in case RowBinaryWithNamesAndTypes function toDateTime(...) uses server time zone while in TSV it uses client time zone. Honestly, I'm not sure what behaviour is right but it definitely should be the same.

Steps to reproduce

1. create client with optionsuse_server_time_zone: false and use_time_zone: Asia/Tokyo
2. do two similar query with different format

Expected behaviour

Same instant

Code example

import java.security.NoSuchAlgorithmException;
import java.time.Instant;
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;

import static java.time.ZoneOffset.UTC;

class CHClientDateBug {
    public static void main(String[] args) throws ClickHouseException, NoSuchAlgorithmException {

        String date = "2019-01-01 17:00:00";

        ClickHouseNodes server = ClickHouseNodes.of("http://localhost:8123");
        ClickHouseConfig config = new ClickHouseConfig(Map.of(
                ClickHouseClientOption.USE_SERVER_TIME_ZONE, false,
                ClickHouseClientOption.USE_TIME_ZONE, "Asia/Tokyo" // UTC+9
        ), null, null, null);
        try (ClickHouseClient client = ClickHouseClient.builder().config(config).build()) {
            Instant fromRowBinary;
            try (ClickHouseResponse response = client.connect(server)
                    .format(ClickHouseFormat.RowBinaryWithNamesAndTypes)
                    .query("SELECT toDateTime('" + date +"')")
                    .executeAndWait()) {
                ClickHouseValue value = response.firstRecord().getValue(0);

                fromRowBinary = value.asInstant();
            }
            Instant fromTSV;
            try (ClickHouseResponse response = client.connect(server)
                    .format(ClickHouseFormat.TabSeparatedWithNamesAndTypes)
                    .query("SELECT toDateTime('" + date +"')")
                    .executeAndWait()) {
                ClickHouseValue value = response.firstRecord().getValue(0);

                fromTSV = value.asInstant();
            }

            // 2019-01-01T17:00Z
            System.out.println(fromRowBinary.atZone(UTC));
            // 2019-01-01T08:00Z
            System.out.println(fromTSV.atZone(UTC));
            // Error
            assert fromTSV.equals(fromRowBinary);
        }
    }
}

Error log

2019-01-01T17:00Z
2019-01-01T08:00Z
Exception in thread "main" java.lang.AssertionError
	at CHClientDateBug.main(CHClientDateBug.java:51)

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]

Honestly, I'm not sure what behaviour is right but it definitely should be the same.

Yes, but as of now, TSV or any other text-based formats are not supported very well compare to RowBinary.

As to the timezone implementation, the implementation is inconsistent with clickhouse-client, the native command line, as described in #1270, which will be fixed.

[misha-nik]

That would be great. Thank you!