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
질문됨 2년 전912회 조회
2개 답변
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
답변함 2년 전
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
답변함 2년 전

로그인하지 않았습니다. 로그인해야 답변을 게시할 수 있습니다.

좋은 답변은 질문에 명확하게 답하고 건설적인 피드백을 제공하며 질문자의 전문적인 성장을 장려합니다.

질문 답변하기에 대한 가이드라인

관련 콘텐츠