Default vs ISO date functions

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?

Thank you

@Jonathan would EXTRACT(DAYOFWEEK ...) - 1 work for you?

Yeah that’s what we ended up doing but I think we are going to switch everything over to using the ISO date functions because it seems to be more accurate for week comparisons.

ISO8601 is always the correct answer :wink:

1 Like