In comparing some of our metrics from Snowflake and Rockset we found some slight differences and my current theory is it’s because of the ISO date functions.
If we use the
EXTRACT(DAYOFWEEK ... function in Rockset it returns values from 1-7
But in Snowflake and Postgres the same function returns values ranging from 0-6 unless we use the ISO version which then returns 1-7. YEAR* / DAY* / WEEK* / MONTH / QUARTER — Snowflake Documentation
A similar situation happens with weeks which I think is a bit more complicated.
We are currently using the non-iso date functions in our reporting and it seems that Rockset only supports ISO versions. Is there a reason we should stick to the ISO version or if we wanted to report in the same way, would I need to create UDFs to achieve it?