Timezone-aware datetime query parameters are internally cast to a wrong timezone

[maxblumental]

Describe the bug

Timezone-aware datetime query parameters are internally cast to a wrong timezone.

Steps to reproduce

1. Use a database whose timezone is EST (UTC-05:00):

SELECT timezone();
-- EST

2. Make a Client with default parameters:

import clickhouse_connect

client = clickhouse_connect.get_client(
    username='...',
    password='...',
    host='...',
    port=443,
    database='...',
)

3. Make a query with a timezone-aware datetime parameter in UTC+3:

import datetime
tzinfo = datetime.timezone(offset=datetime.timedelta(hours=3)) # UTC+3
dt = datetime.datetime(2024, 7, 23, 12, 0, 0, tzinfo=tzinfo)
result = client.query('select toString(%s)', parameters=(dt,))
print(result.result_rows[0][0])
# 2024-07-23 09:00:00

It should have converted UTC+3 to UTC-5 and get 2024-07-23 04:00:00.

Where it happens

As we can see in the debugger, the function clickhouse_connect.driver.query.py:format_query_value is casting the parameter to UTC:

    if isinstance(value, datetime):
        if value.tzinfo is not None or server_tz != pytz.UTC:
            value = value.astimezone(server_tz) # <-- here
        return f"'{value.strftime('%Y-%m-%d %H:%M:%S')}'"

The format_query_value() was called from finalize_query():

def finalize_query(query: str, parameters: Optional[Union[Sequence, Dict[str, Any]]],
                   server_tz: Optional[tzinfo] = None) -> str:
    while query.endswith(';'):
        query = query[:-1]
    if not parameters:
        return query
    if hasattr(parameters, 'items'):
        return query % {k: format_query_value(v, server_tz) for k, v in parameters.items()}
    return query % tuple(format_query_value(v) for v in parameters) # <-- here

For some reason, server_tz wasn't passed to format_query_value() and pytz.UTC default value was used.

Configuration

Environment

• clickhouse-connect version: 0.7.16
• Python version: 3.9.6
• Operating system: macOS Ventura 13.5.2

ClickHouse server

• ClickHouse Server version: 23.8.8.21.altinitystable

[genzgd]

Thanks for the report! server_tz has actually been broken for a while and no one seemed to notice. This should be fixed in 0.7.17

[maxblumental]

@genzgd Could you please also pass the server_tz to format_query_value() in the case when parameters are not a dictionary:

def finalize_query(query: str, parameters: Optional[Union[Sequence, Dict[str, Any]]],
                   server_tz: Optional[tzinfo] = None) -> str:
    while query.endswith(';'):
        query = query[:-1]
    if not parameters:
        return query
    if hasattr(parameters, 'items'):
        return query % {k: format_query_value(v, server_tz) for k, v in parameters.items()}
    return query % tuple(format_query_value(v) for v in parameters) # <<<---- EDIT THIS LINE