Metabase week time dimension error

[itestyoy]

When we use week as granularity in Metabase, Metabase create query

SELECT (CAST(DATE_TRUNC('week', ("public"."analytics_analysis_view"."date" + INTERVAL '1 day')) AS timestamp) + INTERVAL '-1 day') AS "date", SUM("public"."analytics_analysis_view"."sessions_start_count") AS "sum"
FROM "public"."analytics_analysis_view"
GROUP BY (CAST(DATE_TRUNC('week', ("public"."analytics_analysis_view"."date" + INTERVAL '1 day')) AS timestamp) + INTERVAL '-1 day')
ORDER BY (CAST(DATE_TRUNC('week', ("public"."analytics_analysis_view"."date" + INTERVAL '1 day')) AS timestamp) + INTERVAL '-1 day') ASC

and then cube throw an error

Error during rewrite: Can't detect Cube query and it may be not supported yet. Please check logs for additional information.

[paveltiunov]

@itestyoy Could you please try to run this query with CUBESQL_SQL_PUSH_DOWN=true enabled on the latest version? Also, you can update metabase settings to use Monday week instead of Sunday.

[igorlukanin]

@itestyoy Did Pavel's advice help?

[itestyoy]

@igorlukanin Yes, we started using Monday week instead of Sunday. Thanks.

With env CUBESQL_SQL_PUSH_DOWN=true we got error:

Arrow error: Compute error: Error: Parameter types must be provided for null values via the 'types' field in query options

May be it related, may be no, but this query doesn't work

Can't detect Cube query and it may not be supported yet. Please check logs for additional information.

SELECT CAST("public"."analytics_view"."user_first_touch" AS date) AS "user_first_touch" 
FROM "public"."analytics_view" 
WHERE CAST("public"."analytics_view"."user_first_touch" AS date) = date '2024-03-26' 
GROUP BY CAST("public"."analytics_view"."user_first_touch" AS date) 

this works correctly:

SELECT CAST("public"."analytics_view"."user_first_touch" AS date) AS "user_first_touch" 
FROM "public"."analytics_view" 
WHERE "public"."analytics_view"."user_first_touch" = date '2024-03-26' 
GROUP BY CAST("public"."analytics_view"."user_first_touch" AS date)