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

sqldatabasefunctiondatetimeamazon-redshift


Solution 1:[1]

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.

Solution 2:[2]

SELECT TO_CHAR(created_at + interval '8 hours', 'IYYY-IW');

"IW": ISO week number of year (the first Thursday of the new year is in week 1.)

Redshift Docs

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tim Biegeleisen
Solution 2 feng ce

Related Questions