ClickHouse
problem with dates + timezones
This issue arises in v.0.3.2
Suppose a Clickhouse server is located in UTC+0 timezone, while the client is on UTC-x timezone. Then the following query
SELECT toDate('2022-10-27')
will return 2022-10-26 on the client side.
It seems that somewhere inside Java the date '2022-10-27' turns into '2022-10-27 00:00:00 UTC', which is the same as '2022-10-26 20:00:00 NY(UTC-4)', and the latter converts to '2022-10-26'.
Legacy version (v.0.2.6) works fine by the way.
Thanks for the report @mtselishchev, it does sound like an issue. However, I was not able to reproduce the issue using v0.3.2-patch11 and v0.3.2. Could you share code snippet or the database client you're using?
@zhicwu I'm using dbeaver v. 22.2.3 with JDBC driver v.0.3.2 If that matters Clickhouse version is 22.2.2 revision 54455. And yeah, client's Java is OpenJDK 11
Guys,
I have been using clickhouse and clickhouse-jdbc for a year now in my current company and I want to start to contribute now.
This issue seems interesting to me and I have started looking into it.
Can anyone please give me some pointers for getting started?
Thanks @DEBOJYOTI11, that'd be great.
Do you want to investigate this specific issue? If not, perhaps you can start with unit test and integration test for the JDBC driver and Java client, and then later performance tuning. If you prefer to start with new feature, I think you may introduce ClickHouse query parameter to replace named parameter.
Triage Report
- requires more date-time examples
com.clickhouse.client.config.ClickHouseClientOption#USE_SERVER_TIME_ZONE_FOR_DATESshould be a true by default
All Date and Time values should be normalized to modern Date/Time classes like LocaDate. Then this classes should be used to convert into a string representing value. Handling different configuration layers regarding timezone is a mistake and leads to errors.
JDBC should use Calendar methods to specify timezone or use LocaDate/LocaTime to set values and avoid additional conversion.