Search code examples
sqldatabasefunctiondatetimeamazon-redshift

YYYYWW format in SQL Redshift

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))

Solution

  • 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.