Amazon - Redshift : Week number for a given date is coming wrong

Question

Considering Sunday as the first day of the week the below statements in redshift should return 2 as the week number instead returning 1.

Mysql has a function called Week() where we can use the mode to get the desired result. Likewise is there any function in redshift that I could use to get the correct week number.

Any help would be really appreciated.

SELECT EXTRACT(WEEK FROM TIMESTAMP '2018-01-07');

SELECT TO_CHAR(TIMESTAMP '2018-01-07','WW');

Solution 1

date_part can be directly used for your use-case.

Query:

select date_part(w, '2018-01-07');

Here's a link for more details: https://docs.aws.amazon.com/redshift/latest/dg/r_DATE_PART_function.html

Solution 2

There seems to be no direct solution to this, but here's a workaround:

SELECT EXTRACT(WEEK FROM TIMESTAMP '2018-01-07'::date + '1 day'::interval);  

Source

Comments

[Yankee]

It returns the same on PostgreSQL, so it should work the same way on Redshift. That's because the weeks begin on Mondays in both. Try querying for '2017-01-01', which was a Sunday, you'll get 52.

[Jorge Madrigal]

@Yankee, are you saying that on redshift the week starts on Monday? I thought it started on Sunday, is there a way to change this

[Yankee]

@JorgeMadrigal Yes it does, because it's the same in Postgres. Here's something I found to change that in Postgres, should work in Redshift as well blog.0101010.com/making-date-trunc-use-a-sunday-week