Day Of Week Issue

Wondering why metabase adds in some funky-ness when extracting day of week from a date column:

SELECT
PROCESSED_AT,
extract(dayofweek,PROCESSED_AT) correct_way,
CASE WHEN ((date_part("dayofweek", CAST("ANALYTICS"."DBT_PRODUCTION"."ORDERS"."PROCESSED_AT" AS timestamp)) + -1) % 7) = 0 THEN 7 ELSE ((date_part("dayofweek", CAST("ANALYTICS"."DBT_PRODUCTION"."ORDERS"."PROCESSED_AT" AS timestamp)) + -1) % 7) END AS metabase_way
from orders
order by 1 desc

Given yesterday was a tuesday, it should be 2, not 1. any idea whats going on here? for context i'm comparing using the ask a question tool vs SQLing myself

Hi @rjn2
Please post “Diagnostic Info” from Admin > Troubleshooting, and which database type you are querying.
What have you configured as start of week in Admin > Settings?
You can see the code here: https://github.com/metabase/metabase/pull/13090

Thanks @flamber - I see that I have week set to starting Monday, but that still doesnt make sense to me.

Digging deeper, is metabase doing some date conversions without telling me? I'm getting different date data in metabase, than when compared to my data in snowflake? What am i missing?

09%20AM

@rjn2

Please post “Diagnostic Info” from Admin > Troubleshooting, and which database type you are querying.

Snowflake does not support Report Timezone:
https://www.metabase.com/docs/latest/troubleshooting-guide/timezones.html

You’re likely seeing this:
https://github.com/metabase/metabase/issues/8804 - upvote by clicking :+1: on the first post