I found multiple issues linked to how databases drivers interprete weeks depending on which day is chosen as a start day (example : 18301).
We 're using Postgres Read Replica for live data report and Snowflake for analytics .
It apears that if I set Monday as First Day Of Week and run a report on a Monday with Previous Week filter on postgres it returns the right data :
SELECT CAST("public"."shotguns"."created_at" AS date) AS "created_at", count(*) AS "count" FROM "public"."shotguns" WHERE ("public"."shotguns"."created_at" >= date_trunc('week', CAST((CAST(now() AS timestamp) + (INTERVAL '-1 week')) AS timestamp)) AND "public"."shotguns"."created_at" < date_trunc('week', CAST(now() AS timestamp))) GROUP BY CAST("public"."shotguns"."created_at" AS date) ORDER BY CAST("public"."shotguns"."created_at" AS date) ASC
And on Snowflake it is the week before that is returned :
SELECT date_trunc("day", CAST("PRODUCTION_DB_PUBLIC"."SHOTGUNS"."CREATED_AT" AS timestamp)) AS "CREATED_AT", count(*) AS "count" FROM "RAW"."PRODUCTION_DB_PUBLIC"."SHOTGUNS" WHERE ("PRODUCTION_DB_PUBLIC"."SHOTGUNS"."CREATED_AT" >= dateadd(day, 1, CAST(date_trunc("week", CAST(dateadd(day, -1, CAST(dateadd(week, -1, CAST(current_timestamp() AS timestamp)) AS timestamp)) AS timestamp)) AS timestamp)) AND "PRODUCTION_DB_PUBLIC"."SHOTGUNS"."CREATED_AT" < dateadd(day, 1, CAST(date_trunc("week", CAST(dateadd(day, -1, CAST(current_timestamp() AS timestamp)) AS timestamp)) AS timestamp))) GROUP BY date_trunc("day", CAST("PRODUCTION_DB_PUBLIC"."SHOTGUNS"."CREATED_AT" AS timestamp)) ORDER BY date_trunc("day", CAST("PRODUCTION_DB_PUBLIC"."SHOTGUNS"."CREATED_AT" AS timestamp)) ASC
Flamber if you're the one reading this first be indulgent, I looked for existing topics and issues/PR but couldn't find one related to Week Filters so feel free to redirect me in case there is one
I looked at those issue but they do not refer explicitely to datetrunc / dateadd logic linked to the Previous x week filter capability. So I think it's linked but not entirely.
@gchev If you want the correct day returned on Snowflake, then set the connection string WEEK_START=7 in Admin > Databases > (your-db) > Advanced.
And do a browser refresh.
Timezone (including DST and DoW) gets really complicated.
@flamber me again, it looks like the issue rose again . This time I believe the issue comes from the GUI in filters. Yet in the admin setting the week start day is set to Monday. Maybe it's linked to the new capability of changing the anchor to which the previous period is compared.
See some screenshot for investigations : I would have expected previous week to return May 23 - May 29 if start of week is Monday
@gchev That connection string parameter does not have any effect on Snowflake since version 0.43.0, so remove it.
What you are seeing is a frontend issue in the preview text, not the actual results. https://github.com/metabase/metabase/issues/23142 - upvote by clicking on the first post
on the first post