Day Of Week Issue

#1

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

16%20PM
Screen Shot 2021-02-16 at 7.26.16 PM.png533×607 38.2 KB

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

#2

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

#4

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

34%20AM
Screen Shot 2021-02-17 at 9.48.34 AM.png1291×310 25.1 KB

#5

@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