metabase-clickhouse-driver
metabase-clickhouse-driver copied to clipboard
"Week" aggregation is inconsistent with setting "Start of the week" is set to "Monday"
Describe the bug Hi! When setting "Start-of-the-week" is set to "Monday" and when we use a query builder then a weekly aggregation query gets wrong result - the first day of the aggregation is the Sunday of previous week.
To Reproduce Steps to reproduce the behavior:
- Select "Monday" as "Start of the week" in Admin settings.
- Aggregate data on "Week"
- Find data belonging to previous week with Sunday as first day of week.
Expected behavior The first day of week is Monday.
Example
- Prepare a query in the query builder with filter on date field
- Get results from previous week with wrong first day (02.01.22, 09.01.22 and 16.01.22 are Sundays)
Here is the query that MB generates:
SELECT (toMonday((toDate(`DB`.`TABLE`.`COLUMN`) + 1)) - 1) AS `COLUMN`, count(*) AS `count`
FROM `DB`.`TABLE`
WHERE (`DB`.`TABLE`.`COLUMN` >= parseDateTimeBestEffort('2022-01-03 00:00:00.000+03:00')
AND `DB`.`TABLE`.`COLUMN` < parseDateTimeBestEffort('2022-01-17 00:00:00.000+03:00'))
GROUP BY (toMonday((toDate(`DB`.`TABLE`.`COLUMN`) + 1)) - 1)
ORDER BY (toMonday((toDate(`DB`.`TABLE`.`COLUMN`) + 1)) - 1) ASC
Executing the query without MB gives the same result.
Setup
- Metabase 0.42.4
- Metabase-clickhouse-driver 0.8.1
And the same behavior on
- Metabase 0.45.2
- Metabase-clickhouse-driver 0.9.2