"Week" aggregation is inconsistent with setting "Start of the week" is set to "Monday"

[ppavlov39]

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:

1. Select "Monday" as "Start of the week" in Admin settings.
2. Aggregate data on "Week"
3. Find data belonging to previous week with Sunday as first day of week.

Expected behavior
The first day of week is Monday.

Example

1. Prepare a query in the query builder with filter on date field
   
2. 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

[slvrtrn]

@ppavlov39, recently, we investigated a time zones issue #122, and the conclusion was that the discrepancy between MB/JVM/system timezones could cause all kinds of problems like that. Could you check if it works in this case too?

[brendavarguez]

Hello! Having a similar issue here (and it also happens on Dbeaver). My columns have America/Mexico_City timezone but when trying to build some metrics with Metabase, it tries to cast as date time and then I end with +6 hours difference. This is a sample of the code that MB runs:

SELECT
    transaction_id,
  transaction_added,
  toStartOfHour(
    CAST(
      `warehouse_prd`.`transactions`.`transaction_added` AS datetime
    )
  ) AS `transaction_added_cast`
FROM
  `warehouse_prd`.`transactions`
 WHERE
    transaction_added > '2023-09-21'

I pointed out this issue a couple of months ago, and I believe Alexei suggested a feature enhancement request. Was it created? I would like to track it. We are about to open the new db to the entire company. Thanks in advance!

[slvrtrn]

@brendavarguez, I see that the feature request is now created: ClickHouse/ClickHouse#55072

Until it is resolved, when using CAST, the best bet currently is to set the matching timezone of JVM on your machine like it is described here.

[slvrtrn]

@brendavarguez, another possible workaround, perhaps a better one than setting a JVM timezone if you have multiple timezones in your DateTime64 columns, is to create timezone-related questions using the SQL editor.

Consider this dataset: https://fiddle.clickhouse.com/c778ba77-508b-49dc-8568-3264470dc0c7

CREATE TABLE wares (
  id UInt64,
  name String,
  createdAt DateTime('America/Mexico_City')
) ENGINE MergeTree ORDER BY id;

INSERT INTO wares VALUES
(1, 'foo', toDateTime('2023-01-01 04:00:00', 'America/Mexico_City')), (2, 'bar', toDateTime('2023-01-01 04:01:00', 'America/Mexico_City'));

^ which yields correct results, as we enforce the timezone in parseDateTime64BestEffort call.

EDIT: simpler examples