metabase-clickhouse-driver
metabase-clickhouse-driver copied to clipboard
driver issue on toDate function inconsistent result with native cli
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
@AfrouzMashayekhi, are Metabase and ClickHouse timezones the same?
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, are Metabase and ClickHouse timezones the same?
@slvrtrn Yes, both in UTC timezone
@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