My company recently migrated to Redshift from Redash recently and I'm having a hard time recreating the YEARWEEK() function available in MySQL but not in Redshift. I've searched high and low to find a way to do this but can't seem to find the correct resource as I'm also pressed for time.
table: order
column: created_at
stored as: 29/03/21 03:02 -- add 8 hours here via date_add()
desired output: 202113 -- yearweek() yyyyww
In MySQL this gives me the correct output YEARWEEK(DATE_ADD(o.created_at, INTERVAL 8 HOUR))
You should be able to use DATE_PART
here along these lines:
CONCAT(DATE_PART(y, o.created_at + interval '8 hours'),
DATE_PART(w, o.created_at + interval '8 hours'))
Note that it is not clear if the above would produce identical output to MySQL's YEARWEEK
function.