Query settings session_timezone not being applied

[tecpcarrier]

Describe the bug

Clickhouse allows user to set query to multiple level including at the query level. When the setting session_timezone is set in dbeaver , the setting is not being picked up. I have looked at this issue #604 where the timezone is set correctly for toDateTime but it does not seem to be the case when using this setting. Whenever I use session_timezone, the datetime uses the wrong timezone.

In this query, the date should be identical to the second column not the third. Running it directly using clickhouse-client, the result of this query should be the following:

By changing the session_timezone, the value in the first column should change to adapt to the new timezone.

Configuration

ClickHouse server

• ClickHouse Server version: 23.8.2.7
• Statement:

WITH temp AS
    (
        SELECT 1696431276 AS date
    )
SELECT
    toDateTime(date),
    toDateTime(date, 'America/New_York'),
    toDateTime(date, 'UTC')
FROM temp
SETTINGS session_timezone = 'America/Vancouver'

[mohaidoss]

Hey any workaround @tecpcarrier ? I am having the same issue, and it's quite confusing since the following test is doing just fine with the JDBC...

SELECT toDateTime('2023-01-01 10:15:20') SETTINGS session_timezone = 'Etc/GMT+5'

[vladaman]

We are experiencing same issue. Seems like SETTINGS session_timezone = 'Etc/GMT+5' has no effect on result.

[den-crane]

The reason is in Clickhouse server

• session_timezone is not reflected in X-ClickHouse-Timezone ClickHouse#63359

[mohaidoss]

@den-crane seems like the bug is fixed on versions >= 23.8 ?

[chernser]

@mohaidoss There is a bug on client side - value from the server timezone header is not applied to results (only for RowBinary*)
I'm working on the fix.

[chernser]

Client today doesn't read X-Server-Timezone from a response.
The toDateTime('2023-10-04 10:54:36') will be in server or session timezone and thus no conversion should be:

SELECT
    toDateTime('2023-10-04 10:54:36'),
    serverTimezone(),
    timezone(),
    toDateTime('2023-10-04 10:54:36', 'UTC')
SETTINGS session_timezone = 'America/Los_Angeles'

Query id: d78c4447-fe88-40e4-9a61-7033f2069cb2

   ┌─toDateTime('2023-10-04 10:54:36')─┬─serverTimezone()─┬─timezone()──────────┬─toDateTime('2023-10-04 10:54:36', 'UTC')─┐
1. │               2023-10-04 10:54:36 │ UTC              │ America/Los_Angeles │                      2023-10-04 10:54:36 │
   └───────────────────────────────────┴──────────────────┴─────────────────────┴──────────────────────────────────────────┘

After the fix about the header client would convert all DateTime values that come without an offset into server timezone that is returned in response. However it will not affect values that have timezone already.
And this behavior will work only if client is configured to use server timezone.

[mohaidoss]

Thats Exciting @chernser. Let le know if I can be of help :)