clickhouse jdbc 升级，解决时间问题

一、准备

1.问题

之前 clickhouse 库中和 jdbc 查询的时间是正确的，都是 8 时区的时。
有一天升级 mybatis，发现 clickhouse 库中是 8 时区的，mybatis 查询的是 0 时区的。
可以用升级 clickhouse jdbc 驱动来解决这个问题。

clickohuse jdbc 由 0.3.1 升级到 0.3.2-patch11 后，解决问题

2.环境

clickhouse server 版本：clickhouse/clickhouse-server:23.1.2.9

创建测试表：

CREATE TABLE default.test_time
(

    `id` Int64,
    `datetime_1` DateTime,
    `datetime64_1` DateTime64(3)
)
ENGINE = MergeTree
PRIMARY KEY id
ORDER BY id;

二、clickhouse-jdbc 0.3.1

1.clickhouse-jdbc 版本

<dependency>
    <groupId>ru.yandex.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.3.1</version>
</dependency>

<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13.2</version>
    <scope>test</scope>
</dependency>

2.java 代码

@Test
public void test12() throws SQLException {
    String url = "jdbc:clickhouse://127.0.0.1:8123/default";
    String user = "default";
    String password  = null;

    String sql = "SELECT id, datetime64_1 FROM default.test_time WHERE datetime64_1 <= ?";
    LocalDateTime now = LocalDateTime.now();

    Connection connection = DriverManager.getConnection(url, user, password);
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    preparedStatement.setObject(1, now);

    // 生成的 sql
    // 类似，SELECT id, datetime64_1 FROM default.test_time WHERE datetime64_1 <= '2024-05-16 23:59:59';

    ResultSet resultSet = preparedStatement.executeQuery();
    while (resultSet.next()) {
        Object id = resultSet.getObject(1);
        Object datetime = resultSet.getObject(2); // 实际对象，java.sql.Timestamp
        LocalDateTime localDateTime = resultSet.getObject(2, LocalDateTime.class);  
        System.out.println(String.format("%s %s %s", id, datetime, localDateTime));
    }
}

结果如下：

1 2024-05-16 20:21:59.0 2024-05-16T12:21:59
2 2024-05-16 20:22:03.0 2024-05-16T12:22:03

3.分析结果之 resultSet.getObject(columnIndex, LocalDateTime.class)

执行 resultSet.getObject(columnIndex, LocalDateTime.class)，进入 ClickHouseResultSet

// ClickHouseResultSet.java
@Override
public <T> T getObject(int columnIndex, Class<T> type) throws SQLException {
    if (String.class.equals(type)) {
        return (T) getString(columnIndex);
    }

    ClickHouseColumnInfo columnInfo = getColumnInfo(columnIndex);
    TimeZone tz = getEffectiveTimeZone(columnInfo);
    return columnInfo.isArray()
        ? (Array.class.isAssignableFrom(type) ? (T) getArray(columnIndex) : (T) getArray(columnIndex).getArray())
        : ClickHouseValueParser.getParser(type).parse(getValue(columnIndex), columnInfo, tz);
}

ClickHouseValueParser.getParser(type) 得到 ClickHouseLocalDateTimeParser，后执行其 parse

// ClickHouseDateValueParser.java
@Override
public T parse(ByteFragment value, ClickHouseColumnInfo columnInfo, TimeZone timeZone) throws ClickHouseException {

    if (value.isNull()) {
        return null;
    }

    String s = value.asString();

    /*
     *  filter default values for relevant data types,
     *  even if the column has nullable flag set.
     */
    if (PATTERN_EMPTY_DATE.matcher(s).matches()) {
        return null;
    }

    switch (columnInfo.getEffectiveClickHouseDataType()) {
    case Date:
        try {
            return parseDate(s, columnInfo, timeZone);
        } catch (Exception e) {
            throw new ClickHouseUnknownException(
                "Error parsing '" + s + "' of data type '"
                    + columnInfo.getOriginalTypeName()
                    + "' as " + clazz.getName(),
                e);
        }
    case DateTime:
    case DateTime32:
    case DateTime64:
        try {
            return parseDateTime(s, columnInfo, timeZone);
        } catch (Exception e) {
            throw new ClickHouseUnknownException(
                "Error parsing '" + s + "' of data type '"
                    + columnInfo.getOriginalTypeName()
                    + "' as " + clazz.getName(),
                e);
        }
    case Int8:
    case Int16:
    case Int32:
    case Int64:
    case UInt8:
    case UInt16:
    case UInt32:
        try {
            long l = Long.parseLong(s);
            return parseNumber(l, columnInfo, timeZone);
        } catch (Exception e) {
            throw new ClickHouseUnknownException(
                "Error parsing '" + s + "' of data type '"
                    + columnInfo.getOriginalTypeName()
                    + "' as " + clazz.getName(),
                e);
        }
    case UInt64:
        // If we have a large nanos value, we trim to millis
        try {
            BigInteger bi = new BigInteger(s);
            if (bi.compareTo(BigInteger.valueOf(Long.MAX_VALUE)) > 0) {
                return parseNumber(
                    bi.divide(BigInteger.valueOf(1000_000L)).longValue(),
                    columnInfo,
                    timeZone);
            }
            return parseNumber(bi.longValue(), columnInfo, timeZone);
        } catch (Exception e) {
            throw new ClickHouseUnknownException(
                "Error parsing '" + s + "' of data type '"
                    + columnInfo.getOriginalTypeName()
                    + "' as " + clazz.getName(),
                e);
        }
    case String:
    case Unknown:
        try {
            return parseOther(s, columnInfo, timeZone);
        } catch (Exception e) {
            throw new ClickHouseUnknownException(
                "Error parsing '" + s + "' as " + clazz.getName(), e);
        }
    default:
        throw new ClickHouseUnknownException(
            "Error parsing '" + s + "' of data type '"
                + columnInfo.getOriginalTypeName()
                + "' as " + clazz.getName(),
            null);
    }

}

ClickHouseDateValueParser 把 string 转化为 LocalDateTime，把 LocalDateTime 转化为 0 时区

// ClickHouseLocalDateTimeParser.java
@Override
LocalDateTime parseDateTime(String value, ClickHouseColumnInfo columnInfo,
    TimeZone timeZone)
{
    return dateTimeToLocalDateTime(value, columnInfo, timeZone);
}

protected LocalDateTime dateTimeToLocalDateTime(String value, ClickHouseColumnInfo columnInfo, TimeZone timeZone) {
    TimeZone serverTimeZone = columnInfo.getTimeZone();
    LocalDateTime localDateTime = parseAsLocalDateTime(value);
    if (serverTimeZone != null
        && (serverTimeZone.useDaylightTime() || serverTimeZone.getRawOffset() > 0)) { // non-UTC
        localDateTime = localDateTime.atZone(columnInfo.getTimeZone().toZoneId())
            .withZoneSameInstant(java.time.ZoneId.of("UTC")).toLocalDateTime();
    }

    return localDateTime;
}

三、clickhouse-jdbc 0.3.2-patch11

1.clickhouse-jdbc 版本

<dependency>
    <groupId>com.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.3.2-patch11</version>
</dependency>

<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13.2</version>
    <scope>test</scope>
</dependency>

2.java 代码

@Test
public void test22() throws SQLException {
    String url = "jdbc:clickhouse://127.0.0.1:8123/default";
    String user = "default";
    String password  = null;

    String sql = "SELECT id, datetime64_1 FROM default.test_time WHERE datetime64_1 <= ?";
    LocalDateTime now = LocalDateTime.now();

    Connection connection = DriverManager.getConnection(url, user, password);
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    preparedStatement.setObject(1, now);

    // 生成的 sql
    // 类似，SELECT id, datetime64_1 FROM default.test_time WHERE datetime64_1 <= '2024-05-16 23:59:59.999';

    ResultSet resultSet = preparedStatement.executeQuery();
    while (resultSet.next()) {
        Object id = resultSet.getObject(1);
        Object datetime = resultSet.getObject(2);  // 实际对象，java.time.LocalDateTime
        LocalDateTime localDateTime = resultSet.getObject(2, LocalDateTime.class);
        System.out.println(String.format("%s %s %s", id, datetime, localDateTime));
    }
}

结果如下：

1 2024-05-16T20:21:59 2024-05-16T20:21:59
2 2024-05-16T20:22:03 2024-05-16T20:22:03

3.分析结果之 resultSet.next

执行 resultSet.next()，进入 ClickHouseResultSet 的 next() 方法

// ClickHouseResultSet.java
@Override
public boolean next() throws SQLException {
    ensureOpen();

    lastReadColumn = 0;
    boolean hasNext = true;
    if (hasNext()) {
        try {
            currentRow = rowCursor.next();
        } catch (UncheckedIOException e) {
            throw SqlExceptionUtils.handle(e);
        }
        rowNumber++;
    } else {
        currentRow = null;
        hasNext = false;
    }
    return hasNext;
}

ClickHouseDataProcessor$RecordsIterator

// ClickHouseDataProcessor$RecordsIterator.java
public ClickHouseRecord next() {
    return this.processor.nextRecord();
}

ClickHouseDataProcessor 执行 nextRecord 获得数据

// ClickHouseDataProcessor.java
private ClickHouseRecord nextRecord() throws NoSuchElementException, UncheckedIOException {
    ClickHouseRecord r = this.config.isReuseValueWrapper() ? this.currentRecord : this.currentRecord.copy();

    try {
        this.readAndFill(r);
        this.readPosition = 0;
        return r;
    } catch (EOFException var3) {
        if (this.readPosition == 0) {
            throw new NoSuchElementException("No more record");
        } else {
            throw new UncheckedIOException(ClickHouseUtils.format("Reached end of the stream when reading column #%d of %d: %s", new Object[]{this.readPosition + 1, this.columns.length, this.columns[this.readPosition]}), var3);
        }
    } catch (IOException var4) {
        throw new UncheckedIOException(ClickHouseUtils.format("Failed to read column #%d of %d: %s", new Object[]{this.readPosition + 1, this.columns.length, this.columns[this.readPosition]}), var4);
    }
}

使用 BinaryStreamUtils.readDateTime64，把字符串转化为 LocalDateTime

// ClickHouseRowBinaryProcessor.java
protected void readAndFill(ClickHouseRecord r) throws IOException {
    MappedFunctions m = getMappedFunctions();
    int p = this.readPosition;
    ClickHouseColumn[] c = this.columns;
    ClickHouseConfig f = this.config;
    ClickHouseInputStream i = this.input;
    ClickHouseValue[] t = this.templates;

    try {
        for(int len = c.length; p < len; ++p) {
            ClickHouseValue n = m.deserialize(r.getValue(p), f, c[p], i);
            if (n != t[p]) {
                t[p] = n;
            }
        }
    } finally {
        this.readPosition = p;
    }

}

private void buildMappingsForDataTypes() {
    ClickHouseRowBinaryProcessor.buildMappings(this.deserializers, this.serializers, (r, f, c, i) -> {
        return ClickHouseEnumValue.of(r, c.getEnumConstants(), i.readByte());
    }, (v, f, c, o) -> {
        o.writeByte(v.asByte());
    }, new ClickHouseDataType[]{ClickHouseDataType.Enum, ClickHouseDataType.Enum8});
    ...
    ... 

    ClickHouseRowBinaryProcessor.buildMappings(this.deserializers, this.serializers, (r, f, c, i) -> {
        return (ClickHouseValue)(c.getTimeZone() == null ? ClickHouseDateTimeValue.of(r, BinaryStreamUtils.readDateTime(i, f.getUseTimeZone()), 0, f.getUseTimeZone()) : ClickHouseOffsetDateTimeValue.of(r, BinaryStreamUtils.readDateTime(i, c.getTimeZone()), 0, c.getTimeZone()));
}, (v, f, c, o) -> {
        BinaryStreamUtils.writeDateTime32(o, v.asDateTime(), c.getTimeZoneOrDefault(f.getUseTimeZone()));
}, new ClickHouseDataType[]{ClickHouseDataType.DateTime32});

    ClickHouseRowBinaryProcessor.buildMappings(this.deserializers, this.serializers, (r, f, c, i) -> {
        return (ClickHouseValue)(c.getTimeZone() == null ? ClickHouseDateTimeValue.of(r, BinaryStreamUtils.readDateTime64(i, c.getScale(), f.getUseTimeZone()), c.getScale(), f.getUseTimeZone()) : ClickHouseOffsetDateTimeValue.of(r, BinaryStreamUtils.readDateTime64(i, c.getScale(), c.getTimeZone()), c.getScale(), c.getTimeZone()));
    }, (v, f, c, o) -> {
        BinaryStreamUtils.writeDateTime64(o, v.asDateTime(), c.getScale(), c.getTimeZoneOrDefault(f.getUseTimeZone()));
    }, new ClickHouseDataType[]{ClickHouseDataType.DateTime64});
}

// BinaryStreamUtils.java
public static LocalDateTime readDateTime64(ClickHouseInputStream input, int scale, TimeZone tz) throws IOException {
    long value = readInt64(input);
    int nanoSeconds = 0;
    if (ClickHouseChecker.between(scale, "scale", 0, 9) > 0) {
        int factor = BASES[scale];
        nanoSeconds = (int)(value % (long)factor);
        value /= (long)factor;
        if (nanoSeconds < 0) {
            nanoSeconds += factor;
            --value;
        }

        if ((long)nanoSeconds > 0L) {
            nanoSeconds *= BASES[9 - scale];
        }
    }

    return LocalDateTime.ofInstant(Instant.ofEpochSecond(value, (long)nanoSeconds), tz != null ? tz.toZoneId() : ClickHouseValues.UTC_ZONE);
}

则在 resultSet.getObject(columnIndex) 时，直接得到 LocalDateTime

四、解决问题

可改为使用 resultSet.getObject 方式，取得数据

另：猜测，clickhouse jdbc 驱动升级，通过 resultSet.getObject(columnIndex) 查询 DateTime64 类型嗯，得到的实际对象，从 java.sql.Timestamp 变化为 java.time.LocalDateTime，是为了支持 JSR310，使用全新的时间API。