Invalid Week number returned

0

Hello Redshift team,

I am trying to find a way to return the week number as 1 when date is 1st Jan each year. While testing for these years (2000,2005,2010,2011,2012,2016,2017,2021,2022), the value returned for week number is 52, instead of 1 .

-- Query 1 (output 52)  
select extract(week from '2022-01-01'::date);  
select date_part(w, '2022-01-01'::date);

-- Query 2  (output 52)  
select extract(week from '2022-01-02'::date);  
select date_part(w, '2022-01-02'::date);

-- Query 3  (output 2022-01-01)  
-- Query to find the first week for the year 2022  
select to_date(1||' '||2022,'WW YYYY')

Much appreciate if you can please clarify this for me

  1. 1st Jan of each year isn't this date supposed to return as week 1 for every calendar year? If not, please let me know what needs to be done.
  2. In 'Query 2', the week returned is 52, despite it falls on 2nd Jan 2022 and on the second week of the month. I am quite not sure why its still returning 52 here.
  3. With 'Query 3', I see the result shows first week of 2022 as 1st Jan, which is what I am after. I am confused why the 'extract' and 'date_part' functions are returning a different week number, different from the 'to_date' function?

I tried setting the session timezone to UTC, but still the results are same. SET timezone to default;

Cluster details:
Version: 1.0.36224
Region: us-east-1
SQL Client: Redshift Query Editor V2

AJP
asked 2 years ago895 views
2 Answers
1

According to timeanddate.com https://www.timeanddate.com/calendar/custom.html?year=2022&month=1&country=22&typ=1&wno=1&display=1&df=1 Week 1 of 2022 starts on 3/Jan/2022 so Query 1 and 2 are responding correctly.

Whereas in Query 3 you it is returning what you originally wanted i.e. "I am trying to find a way to return the week number as 1 when date is 1st Jan each year" Per the definitions listed in the table at https://docs.aws.amazon.com/redshift/latest/dg/r_FORMAT_strings.html WW - Week number of year (1–53; the first week starts on the first day of the year.) So for your use case Query 3 might be the way to go as it returns exactly what you want.

Kishan
answered 2 years ago
0

Kishan, thank you for the quick response.

After going through the links, I see the week number 52 returned for 'Query 1' and 'Query 2' is due to ISO-8601 standard, where years always start on the first Monday closest to January 1st. This may mean that the year does not start until January 4th in some cases, or may start in late December in others. However standard Gregorian calendars always start the year on the 1st of January.

To fix I use "to_char(replace_with_date_value,'IW')" for ISO week numbers, and "to_char(replace_with_date_value,'WW')" for standard Gregorian calendars, which starts from 1-52, counting from first day of the year.

AJP
answered 2 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions