Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support DateTime64 parameter binding #396

Closed
IlluvatarEru opened this issue Sep 19, 2024 · 0 comments · Fixed by #400
Closed

Support DateTime64 parameter binding #396

IlluvatarEru opened this issue Sep 19, 2024 · 0 comments · Fixed by #400
Labels
enhancement New feature or request

Comments

@IlluvatarEru
Copy link

Is your feature request related to a problem? Please describe.
When a column in a table has type DateTime64 I would like to be able to query the column by passing some parameters that will be bound, it's currently not possible so queries do not work as expected.

Describe the solution you'd like
I would like to be able to run the below:

import clickhouse_connect

client = clickhouse_connect.get_client(
    host=CLICKHOUSE_CLOUD_HOSTNAME, 
    username=CLICKHOUSE_CLOUD_USER, 
    password=CLICKHOUSE_CLOUD_PASSWORD
)
q0 = "SELECT DISTINCT timestamp FROM mydb.mytable"
r0 = list(client.query(q).named_results())
print(r0)

"""
Getting the below results:
[{'timestamp': datetime.datetime(2024, 9, 1, 0, 0, 25, 26208, tzinfo=<DstTzInfo 'Europe/Berlin' CEST+2:00:00 DST>)}, {'timestamp': datetime.datetime(2024, 9, 1, 0, 0, 25, 27033, tzinfo=<DstTzInfo 'Europe/Berlin' CEST+2:00:00 DST>)}, {'timestamp': datetime.datetime(2024, 9, 1, 0, 0, 25, 27597, tzinfo=<DstTzInfo 'Europe/Berlin' CEST+2:00:00 DST>)}]
"""

Now I want to query the DB to get all the data for the first timestamp here:

dtt = r0[0]['timestamp']

parameters = {"v1":dtt, "query_tz":"UTC"}
# also tried: parameters = {"v1":dtt, "query_tz":"UTC"}
# also tried: parameters = {"v1":dtt.astimezone(pytz.utc), "query_tz":"UTC"}
# also tried: parameters = {"v1":dtt.astimezone(pytz.utc)}

q1 = "SELECT * FROM mydb.mytable WHERE timestamp = {v1:DateTime}"
r1 = client.query(qq, parameters=parameters)
print(list(r1.named_results()))

# result is always empty: []

Describe alternatives you've considered
NA

Additional context

See this SO question for addtional context.

@IlluvatarEru IlluvatarEru added the enhancement New feature or request label Sep 19, 2024
@IlluvatarEru IlluvatarEru changed the title Support DateTime64 parameter Support DateTime64 parameter binding Sep 19, 2024
@genzgd genzgd mentioned this issue Sep 26, 2024
2 tasks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request
Projects
None yet
Development

Successfully merging a pull request may close this issue.

1 participant