一、准备
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。