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:
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?
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?
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?