TimeZone problems

[avraam-inside]

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

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

What is it?

[den-crane]

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

[zhicwu]

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]

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]

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]

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

[zhicwu]

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]

@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]

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