Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
How do I return ISO day of week in Redshift?
I have a query summarizing some transaction data that I'd like to summarize by day of week. For my use case, I need to return weekdays formatted according to ISO 8601, so Monday must be the first day of the week and Sunday the last day of the week.
Postgres/Redshift by default use Sunday as the first day of the week. Thus something like extract(dow from timestamp 'blah') returns 0 if blah is a Sunday, and 1 if a Monday.
Postgres apparently has an isodow argument for exactly this purpose, but it seems like Redshift doesn't support that. Is there a good way to do this other than just wrapping the extract above in a case statement to return the correct values?
1 answer
Quick answer
(DATE_PART(dayofweek, my_datetime) + 6) % 7
Slow answer
For avoidance of doubt, here is what I believe you currently have (assuming an input called my_datetime):
DATE_PART(dayofweek, my_datetime)
Or its abbreviated equivalent:
DATE_PART(dow, my_datetime)
This returns 0 if my_datetime is a Sunday.
You want a number from 0 to 6, representing Monday to Sunday. Instead you have a number from 0 to 6, representing Sunday to Saturday. If there doesn't turn out to be a straightforward way to access the format you want directly, there are 2 possible approaches that will probably be simpler (to write and to read) than using a CASE statement.
Modify the output
If you add 6 to the output, for a Sunday it will now be 6 instead of 0, as required. However, for all the other days of the week it will now be greater than 6. This can be fixed by reducing the answer modulo 7 (that is, taking the remainder after dividing by 7). Now every day of the week has the required number.
(DATE_PART(dayofweek, my_datetime) + 6) % 7
You may find it more intuitive to think of subtracting 1 from the output, rather than adding 6. Personally I avoid negative numbers when using the modulo operator because different languages and different implementations give different results for negative numbers, but they all give consistent results for positive numbers. I include the following in case you find it more readable, but I recommend against using it unless you are certain it will never be used in a different SQL implementation:
(DATE_PART(dayofweek, my_datetime) - 1) % 7
Note that the documentation for Redshift's MOD operator does not currently specify its behaviour with negative numbers.
Modify the input
Instead of modifying the output, you could ask what day of the week it will be in 6 days time, which will always give the correct answer:
DATE_PART(dayofweek, DATEADD(day, 6, my_datetime))
You may find it more intuitive to ask what day it was a day earlier, by subtracting 1 day from the input datetime. Personally I avoid negative numbers where possible as some SQL implementations have subtle bugs, such as breaking when crossing a year boundary with a negative offset. I include the following in case you find it more readable but please bear in mind that it should only be used in an implementation that is safe for negative numbers:
DATE_PART(dayofweek, DATEADD(day, -1, my_datetime))
0 comment threads
0 comment threads