time zone issues

[peterZhiFate]

I insert a piece of data into the database INSERT INTO t_date select '2021-07-21 00:00:22'.

Then get this data through the jdbc driver? But the result becomes 2021-07-21 08:00:22....

What is the reason for this?

In addition, I found this data directly on the sql client and the result was correct.

[zhicwu]

Yes, this driver currently has problem dealing with timezone properly. I'd suggest you to avoid string representation of datetime and use unix timestamp instead. See more information in #623.

[den-crane]

What is the reason for this?

jdbc driver (java) automatically converts datetime values into a client timezone (timezone of your desktop).
Clickhouse assumes that you inserted data '2021-07-21 00:00:22' in a server timezone (or column timezone if specified).
clickhouse-client uses clickhouse-server timezone.

not a bug, other clients have the same behavior.

[zhicwu]

The behavior is customizable. Assume your clickhouse server's timezone is UTC and client time zone(default time zone in JVM) is Asia/Chongqing:

• by default(use_server_time_zone is true)

  drop table if exists t_date;
  create table t_date (d DateTime) engine=Memory;
  INSERT INTO t_date select '2021-07-21 00:00:22';
  -- below returns 2021-07-21 00:00:22
  select d from t_date

• change use_server_time_zone to false, same query gives you 2021-07-21 08:00:22
• change use_time_zone to Europe/Berlin, and the result will be 2021-07-21 02:00:22