ClickHouseDateValueParser parse LocalDateTime with wrong Time Zone

[fernee]

in the method ru.yandex.clickhouse.response.parser.ClickHouseDateValueParser#dateTimeToLocalDateTime, when columnInfo.getTimeZone() is not utc, the code below will change the time zone to utc, so we got a wrong LocalDateTime instance. what's the purpose of the code below?

https://github.com/ClickHouse/clickhouse-jdbc/blob/549a62f180fbc21d30a6a0dbfed376013669d2f9/clickhouse-jdbc/src/main/java/ru/yandex/clickhouse/response/parser/ClickHouseDateValueParser.java#L44-L54

[zhicwu]

LocalDateTime has no concept of time zone, it just provides a zero offset UTC+0. On the other hand, ClickHouse sends DateTime formatted using server / column timezone in TabSeparated format - the string value is actually a ZonedDateTime, so I had to convert it back to UTC.

Above workaround is no longer needed after switching to a binary format like RowBinary, because ClickHouse will send unix timestamp instead of formatted datetime.

[fernee]

@zhicwu yes, unix timestamp can resolve this problem. and sure, LocalDateTime has no concept of time zone, but it dosen't just provides a zero offset UTC+0. we should decide the time zone, then convert to LocalDateTime. In this case, jdbc client should respect the server/column timezone by default, and has ability to customize the timezone for parse time to LocalDateTime

[zhicwu]

@zhicwu yes, unix timestamp can resolve this problem. and sure, LocalDateTime has no concept of time zone, but it dosen't just provides a zero offset UTC+0. we should decide the time zone, then convert to LocalDateTime. In this case, jdbc client should respect the server/column timezone by default, and has ability to customize the timezone for parse time to LocalDateTime

Not quite sure what exactly you need. It sounds like you should use ZonedDateTime not LocalDateTime, as the latter one has nothing to do with timezone and the driver already took care of timezone conversion automatically for you. Why not convert LocalDateTime to ZonedDateTime using whatever timezone you need?

[fernee]

I think ZonedDateTime is for some cases that across the time zones. In the other hand, LocalDateTime is for cases that only in certain time zone, to simplify the handling of time. UTC just a case of certain time zone, but other time zones should also be supported.

[reedycreek]

@zhicwu I cannot get the same LocalDateTime value that I inserted by using the driver. It is not consistent between the insert (write) and the query(read). Any suggestion?

[zhicwu]

@zhicwu I cannot get the same LocalDateTime value that I inserted by using the driver. It is not consistent between the insert (write) and the query(read). Any suggestion?

Sounds like a bug. What's the timezone settings(server/column/client)? And steps to reproduce the issue?

[zhicwu]

This is again related to the TabSeparated format on which the driver relies :<

When you insert a LocalDateTime, the driver will format the object as String, and then pass it to server in TabSeparated format. ClickHouseValueFormatter.formatLocalDateTime(LocalDateTime) didn't consider timezone, especially when 1) server is in non-UTC timezone; or 2) DateTime* column uses non-UTC timezone. The first issue can be fixed by passing server TimeZone to the method, but the second cannot be easily fixed because the driver does not even know what the column type is.

I'll make the following changes to fix this along with other similar issues:

• use RowBinary format when useRowBinary is set to true - this applies to both read and write(except the former uses RowBinaryWithNamesAndTypes)
• add client-side prepared statement(activate when useClientPrepStmts is true) for insert - it's not going to work for complex queries but should be good enough for simple inserts

Note: above two options will default to false in 0.3.x for backward compatibility, but they will be removed and enabled by default starting from 0.4.0.

[freestyle-coding]

This is again related to the TabSeparated format on which the driver relies :<

When you insert a LocalDateTime, the driver will format the object as String, and then pass it to server in TabSeparated format. ClickHouseValueFormatter.formatLocalDateTime(LocalDateTime) didn't consider timezone, especially when 1) server is in non-UTC timezone; or 2) DateTime* column uses non-UTC timezone. The first issue can be fixed by passing server TimeZone to the method, but the second cannot be easily fixed because the driver does not even know what the column type is.

I'll make the following changes to fix this along with other similar issues:

• use RowBinary format when useRowBinary is set to true - this applies to both read and write(except the former uses RowBinaryWithNamesAndTypes)
• add client-side prepared statement(activate when useClientPrepStmts is true) for insert - it's not going to work for complex queries but should be good enough for simple inserts

Note: above two options will default to false in 0.3.x for backward compatibility, but they will be removed and enabled by default starting from 0.4.0.

so is there anything I can do to work around with 0.3.1 version?

[zhicwu]

@freestyle-coding, a workaround is to use UInt32 as value for DateTime column, for examples:

...
statement.execute('insert into my_table(..., datetime_column, ...) values(..., 1622938546, ...)');
...
preparedStatement.setLong(2, 1622938546L);
...

[zhicwu]

New JDBC driver com.clickhouse.jdbc.ClickHouseDriver now uses RowBinary for serialization/deserialization and has better support for handling DateTime32/DateTime64 and timezone.