driver issue on toDate function inconsistent result with native cli

[AfrouzMashayekhi]

Describe the bug

when using toDate function the result is not the same as not running with toDate or with the result of running on Clickhouse-client

Steps to reproduce

Expected behaviour

returning 10-20 not 10-19

Error log

t = clickhouse_query("""select toDate(published_at) from divar.posts where toDate(published_at)='2023-10-20' limit 10""") print(t.result_rows) toolbox:clikhouse# venv/bin/python command.py [(datetime.date(2023, 10, 20),), (datetime.date(2023, 10, 20),), (datetime.date(2023, 10, 20),), (datetime.date(2023, 10, 20),), (datetime.date(2023, 10, 20),), (datetime.date(2023, 10, 20),), (datetime.date(2023, 10, 20),), (datetime.date(2023, 10, 20),), (datetime.date(2023, 10, 20),), (datetime.date(2023, 10, 20),)]

Configuration

Environment

• metabase-clickhouse-driver version: 1.2.1
• Metabase version: v0.47.4

ClickHouse server

• ClickHouse Server version: 23.3.13.6

[slvrtrn]

@AfrouzMashayekhi, are Metabase and ClickHouse timezones the same?

[niloo-sh]

we have the same problem too.
select now() and select today() show different date. it is interesting that when i use select toString(today()) the output is the same as select now()

toDate(now()) function's output is the same as today() function

Environment

• metabase-clickhouse-driver version: 1.2.3
• Dockerized Metabase version: 0.47.2 timezone: utc
• clickhouse server version: 22.6.1. timezone: utc

[AfrouzMashayekhi]

@AfrouzMashayekhi, are Metabase and ClickHouse timezones the same?

@slvrtrn Yes, both in UTC timezone

[slvrtrn]

@AfrouzMashayekhi @niloo-sh In your instances, what a query such as

WITH arr AS (SELECT arrayMap(x -> format('2023-12-12 {}:08:00', leftPad(toString(x), 2, '0')), range(0, 24)) AS a),
     cte AS (SELECT arrayJoin(a) AS s FROM arr AS s)
SELECT s, toDateTime(s) AS dt, toString(dt) AS dts, toDate(dt) AS d, toString(d) AS ds FROM cte;

prints if executed from the Metabase query editor?

today/now (tied to ClickHouse time, which is tied to Docker time, which is tied to the system time) are quite challenging to test appropriately, so we have to be creative here.

Mine (the system is GMT, CH 23.11 is UTC, MB 0.48.1 is UTC, driver version 1.3.0) gets the following (i.e. no difference between any of these):

[
{"s":"2023-12-12 00:08:00","dt":"2023-12-12T00:08:00","dts":"2023-12-12 00:08:00","d":"2023-12-12","ds":"2023-12-12"},
{"s":"2023-12-12 01:08:00","dt":"2023-12-12T01:08:00","dts":"2023-12-12 01:08:00","d":"2023-12-12","ds":"2023-12-12"},
{"s":"2023-12-12 02:08:00","dt":"2023-12-12T02:08:00","dts":"2023-12-12 02:08:00","d":"2023-12-12","ds":"2023-12-12"},
{"s":"2023-12-12 03:08:00","dt":"2023-12-12T03:08:00","dts":"2023-12-12 03:08:00","d":"2023-12-12","ds":"2023-12-12"},
{"s":"2023-12-12 04:08:00","dt":"2023-12-12T04:08:00","dts":"2023-12-12 04:08:00","d":"2023-12-12","ds":"2023-12-12"},
{"s":"2023-12-12 05:08:00","dt":"2023-12-12T05:08:00","dts":"2023-12-12 05:08:00","d":"2023-12-12","ds":"2023-12-12"},
{"s":"2023-12-12 06:08:00","dt":"2023-12-12T06:08:00","dts":"2023-12-12 06:08:00","d":"2023-12-12","ds":"2023-12-12"},
{"s":"2023-12-12 07:08:00","dt":"2023-12-12T07:08:00","dts":"2023-12-12 07:08:00","d":"2023-12-12","ds":"2023-12-12"},
{"s":"2023-12-12 08:08:00","dt":"2023-12-12T08:08:00","dts":"2023-12-12 08:08:00","d":"2023-12-12","ds":"2023-12-12"},
{"s":"2023-12-12 09:08:00","dt":"2023-12-12T09:08:00","dts":"2023-12-12 09:08:00","d":"2023-12-12","ds":"2023-12-12"},
{"s":"2023-12-12 10:08:00","dt":"2023-12-12T10:08:00","dts":"2023-12-12 10:08:00","d":"2023-12-12","ds":"2023-12-12"},
{"s":"2023-12-12 11:08:00","dt":"2023-12-12T11:08:00","dts":"2023-12-12 11:08:00","d":"2023-12-12","ds":"2023-12-12"},
{"s":"2023-12-12 12:08:00","dt":"2023-12-12T12:08:00","dts":"2023-12-12 12:08:00","d":"2023-12-12","ds":"2023-12-12"},
{"s":"2023-12-12 13:08:00","dt":"2023-12-12T13:08:00","dts":"2023-12-12 13:08:00","d":"2023-12-12","ds":"2023-12-12"},
{"s":"2023-12-12 14:08:00","dt":"2023-12-12T14:08:00","dts":"2023-12-12 14:08:00","d":"2023-12-12","ds":"2023-12-12"},
{"s":"2023-12-12 15:08:00","dt":"2023-12-12T15:08:00","dts":"2023-12-12 15:08:00","d":"2023-12-12","ds":"2023-12-12"},
{"s":"2023-12-12 16:08:00","dt":"2023-12-12T16:08:00","dts":"2023-12-12 16:08:00","d":"2023-12-12","ds":"2023-12-12"},
{"s":"2023-12-12 17:08:00","dt":"2023-12-12T17:08:00","dts":"2023-12-12 17:08:00","d":"2023-12-12","ds":"2023-12-12"},
{"s":"2023-12-12 18:08:00","dt":"2023-12-12T18:08:00","dts":"2023-12-12 18:08:00","d":"2023-12-12","ds":"2023-12-12"},
{"s":"2023-12-12 19:08:00","dt":"2023-12-12T19:08:00","dts":"2023-12-12 19:08:00","d":"2023-12-12","ds":"2023-12-12"},
{"s":"2023-12-12 20:08:00","dt":"2023-12-12T20:08:00","dts":"2023-12-12 20:08:00","d":"2023-12-12","ds":"2023-12-12"},
{"s":"2023-12-12 21:08:00","dt":"2023-12-12T21:08:00","dts":"2023-12-12 21:08:00","d":"2023-12-12","ds":"2023-12-12"},
{"s":"2023-12-12 22:08:00","dt":"2023-12-12T22:08:00","dts":"2023-12-12 22:08:00","d":"2023-12-12","ds":"2023-12-12"},
{"s":"2023-12-12 23:08:00","dt":"2023-12-12T23:08:00","dts":"2023-12-12 23:08:00","d":"2023-12-12","ds":"2023-12-12"}
]

EDIT: it's also possible that this one is somehow related to #200

[slvrtrn]

Closing this due to inactivity; if this issue persists even after 1.50.1, please feel free to re-open/create a new one.