Weekly aggregation is different for columns

Bug reports
1

In Metabase we have set our first day of the week to Monday

image
image588×137 4.09 KB

However in the table most column correctly aggregate to show a week start on Monday, but there is 1 column which incorrectly shows Sunday as the start of the week:
image

{
"browser-info": {
"language": "en-US",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/136.0.0.0 Safari/537.36",
"vendor": "Google Inc."
},
"metabase-info": {
"databases": [
"snowflake",
"mysql"
],
"run-mode": "prod",
"plan-alias": "",
"version": {
"date": "2025-05-06",
"tag": "v0.54.6",
"hash": "d56bf23"
},
"settings": {
"report-timezone": null
},
"hosting-env": "unknown",
"application-database": "mysql",
"application-database-details": {
"database": {
"name": "MySQL",
"version": "8.0.40"
},
"jdbc-driver": {
"name": "MariaDB Connector/J",
"version": "2.7.10"
}
}
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "21.0.7+6-LTS",
"java.vendor": "Eclipse Adoptium",
"java.vendor.url": "https://adoptium.net/",
"java.version": "21.0.7",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "21.0.7+6-LTS",
"os.name": "Linux",
"os.version": "4.14.355-275.572.amzn2.x86_64",
"user.language": "en",
"user.timezone": "UTC"
}
}

2

Hello there,

Could you send us the SQL query it's doing, and the database engine for this particular question?
Also, can you test grouping by week in your other database and let me know if it also happens there?

3

@marcos

The database for this query is "Snowflake"

SQL:
SELECT
"DIM"."DIM_HUBSPOT_DEALS"."DEAL_ID" AS "DEAL_ID",
TO_DATE(
TO_TIMESTAMP_LTZ("DIM"."DIM_HUBSPOT_DEALS"."DEMO_DATUM")
) AS "DEMO_DATUM",
DATE_TRUNC('week', "DIM"."DIM_HUBSPOT_DEALS"."DEMO_DATUM") AS "DEMO_DATUM_2",
TO_DATE(
TO_TIMESTAMP_LTZ(
"DIM"."DIM_HUBSPOT_DEALS"."DEAL_STAGE_TIMESTAMP_BECAME_A_MQL"
)
) AS "DEAL_STAGE_TIMESTAMP_BECAME_A_MQL",
DATE_TRUNC(
'week',
"DIM"."DIM_HUBSPOT_DEALS"."DEAL_STAGE_TIMESTAMP_BECAME_A_MQL"
) AS "DEAL_STAGE_TIMESTAMP_BECAME_A_MQL_2",
COUNT(*) AS "count"
FROM
"REPORTING"."DIM"."DIM_HUBSPOT_DEALS"
WHERE
(
DATE_TRUNC('week', "DIM"."DIM_HUBSPOT_DEALS"."DEMO_DATUM") IS NOT NULL
)

AND (
"DIM"."DIM_HUBSPOT_DEALS"."DEAL_STAGE_TIMESTAMP_BECAME_A_MQL" >= '2025-05-12':: date
)
AND (
"DIM"."DIM_HUBSPOT_DEALS"."DEAL_STAGE_TIMESTAMP_BECAME_A_MQL" < '2025-05-19':: date
)
AND ("DIM"."DIM_HUBSPOT_DEALS"."DEAL_ID" = 37165922062)
GROUP BY
"DIM"."DIM_HUBSPOT_DEALS"."DEAL_ID",
TO_DATE(
TO_TIMESTAMP_LTZ("DIM"."DIM_HUBSPOT_DEALS"."DEMO_DATUM")
),
DATE_TRUNC('week', "DIM"."DIM_HUBSPOT_DEALS"."DEMO_DATUM"),
TO_DATE(
TO_TIMESTAMP_LTZ(
"DIM"."DIM_HUBSPOT_DEALS"."DEAL_STAGE_TIMESTAMP_BECAME_A_MQL"
)
),
DATE_TRUNC(
'week',
"DIM"."DIM_HUBSPOT_DEALS"."DEAL_STAGE_TIMESTAMP_BECAME_A_MQL"
)
ORDER BY
DATE_TRUNC('week', "DIM"."DIM_HUBSPOT_DEALS"."DEMO_DATUM") DESC,
"DIM"."DIM_HUBSPOT_DEALS"."DEAL_ID" ASC,
TO_DATE(
TO_TIMESTAMP_LTZ("DIM"."DIM_HUBSPOT_DEALS"."DEMO_DATUM")
) ASC,
DATE_TRUNC('week', "DIM"."DIM_HUBSPOT_DEALS"."DEMO_DATUM") ASC,
TO_DATE(
TO_TIMESTAMP_LTZ(
"DIM"."DIM_HUBSPOT_DEALS"."DEAL_STAGE_TIMESTAMP_BECAME_A_MQL"
)
) ASC,
DATE_TRUNC(
'week',
"DIM"."DIM_HUBSPOT_DEALS"."DEAL_STAGE_TIMESTAMP_BECAME_A_MQL"
) ASC

Also, can you test grouping by week in your other database and let me know if it also happens there?

Do you mean test this table in our MySQL database or any table in that database?

4

What I meant was double-checking if the offset by week was particularly wrong in Snowflake but not in MySQL for example, but here it's strange because within the same query, one column is correct and the other is wrong, and I don't see any offset being added in the SQL.

What are the DB types of "demo_datum" and "deal_stage_timestamp_became_a_mql"? Also, does setting the "first day of the week" to Sunday yield the same date on both for the query?