ClickHouse/clickhouse-java

TimeZone problems

avraam-inside opened this issue · 8 comments

avraam-inside commented

The code I run in DBeaver (via JDBC) and the result (wrong, timezone left +3 hours)
image

The same code that I run in Tabix and the result (correct, timezone left +0 hours)
image

What is it?

den-crane commented

Nothing is wrong here.
Java displays time in your local timezone.

zhicwu commented

Regardless what server time zone is and use_time_zone is set to, JDBC driver always format timestamp in local timezone, which is very confusing and this is inconsistent with clickhouse-client. Let's take it as a bug. I'll add more test cases to cover this and get it fixed in next release.

Server TimeZone: UTC
Berlin  : [JDBC] 2021-03-25 08:50:56 [Server] 1616633456 [clickhouse-client] 2021-03-25 01:50:56
Shanghai: [JDBC] 2021-03-25 08:50:56 [Server] 1616633456 [clickhouse-client] 2021-03-25 08:50:56
UTC     : [JDBC] 2021-03-25 08:50:56 [Server] 1616633456 [clickhouse-client] 2021-03-25 00:50:56
default : [JDBC] 2021-03-25 08:50:56 [Server] 1616633456 [clickhouse-client] 2021-03-25 00:50:56

-- server: UTC; client: Asia/Chongqing
select toDateTime(1616633456),
	toDateTime(1616633456, 'Etc/UTC'),
	toDateTime(1616633456, 'America/Los_Angeles'),
	toDateTime(1616633456, 'Asia/Chongqing'),
	toDateTime(1616633456, 'Europe/Berlin'),
	toUInt32(toDateTime('2021-03-25 08:50:56')), -- 1616662256
	toUInt32(toDateTime('2021-03-25 08:50:56', 'Asia/Chongqing'))
den-crane commented

which is very confusing and this is inconsistent with clickhouse-client. Let's take it as a bug.

So you are going to break the current behavior? I guess thousands of users will be shocked.

zhicwu commented

So you are going to break the current behavior? I guess thousands of users will be shocked.

Not all of a sudden, of course :D

In 0.3.x, we can introduce another connection setting to enable the changed behavior(disabled by default). Flip in 0.4, and retire the connection setting and old behavior in 1.0.

Let me know if you have better idea to reduce the impact.

enqueue commented

Which method does DBeaver use? Where do you see a bug?

zhicwu commented

As I recalled, it's just a thin wrapper in DBeaver for ClickHouse, so I guess it uses getTimestamp. In order to reproduce the issue, you can use native command-line to issue the query, and then compare the results with what you saw in DBeaver.

TZ='Asia/Shanghai' clickhouse-client --use_client_time_zone true
...
ch-server :) select toDateTime(1616633456), toDateTime(1616633456, 'Etc/UTC'), toDateTime(1616633456, 'America/Los_Angeles'), toDateTime(1616633456, 'Asia/Chongqing'), toDateTime(1616633456, 'Europe/Berlin'), toUInt32(toDateTime('2021-03-25 08:50:56')), toUInt32(toDateTime('2021-03-25 08:50:56', 'Asia/Chongqing'))

SELECT
    toDateTime(1616633456),
    toDateTime(1616633456, 'Etc/UTC'),
    toDateTime(1616633456, 'America/Los_Angeles'),
    toDateTime(1616633456, 'Asia/Chongqing'),
    toDateTime(1616633456, 'Europe/Berlin'),
    toUInt32(toDateTime('2021-03-25 08:50:56')),
    toUInt32(toDateTime('2021-03-25 08:50:56', 'Asia/Chongqing'))

Query id: 4a6eede6-9dfa-4dcc-92fc-3ee5f3dae8d9

┌─toDateTime(1616633456)─┬─toDateTime(1616633456, 'Etc/UTC')─┬─toDateTime(1616633456, 'America/Los_Angeles')─┬─toDateTime(1616633456, 'Asia/Chongqing')─┬─toDateTime(1616633456, 'Europe/Berlin')─┬─toUInt32(toDateTime('2021-03-25 08:50:56'))─┬─toUInt32(toDateTime('2021-03-25 08:50:56', 'Asia/Chongqing'))─┐
│    2021-03-25 08:50:56 │               2021-03-25 00:50:56 │                           2021-03-24 17:50:56 │                      2021-03-25 08:50:56 │                     2021-03-25 01:50:56 │                                  1616662256 │                                                    1616633456 │
└────────────────────────┴───────────────────────────────────┴───────────────────────────────────────────────┴──────────────────────────────────────────┴─────────────────────────────────────────┴─────────────────────────────────────────────┴───────────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.038 sec.

On DBeaver(with default settings, local timezone is Asia/Shanghai), same query gives you below:

toDateTime(1616633456) toDateTime(1616633456, 'Etc/UTC') toDateTime(1616633456, 'America/Los_Angeles') toDateTime(1616633456, 'Asia/Chongqing') toDateTime(1616633456, 'Europe/Berlin') toUInt32(toDateTime('2021-03-25 08:50:56')) toUInt32(toDateTime('2021-03-25 08:50:56', 'Asia/Chongqing'))
2021-03-25 08:50:56 2021-03-25 08:50:56 2021-03-25 08:50:56 2021-03-25 08:50:56 2021-03-25 08:50:56 1616662256 1616633456
zhicwu commented

@enqueue, my memory didn't serve me well. After taking a closer look at DBeaver code, I think it we probably don't need to change anything in the driver. I'll download the code and submit a PR to fix this.

zhicwu commented

Now the new JDBC driver com.clickhouse.jdbc.ClickHouseDriver provides same output as cli client:

  • by default it uses server time zone
  • change use_server_time_zone to false, it will use client time zone(same as default time zone in JVM)
  • change use_time_zone to Asia/Chongqing(and use_server_time_zone=false), it will use the specified time zone
  • 👍3