How can I get proper week number in the start of the year

Question

I am currently involved in developing an app, where I need to sort incoming data, putting it in the tables depending on the year and the week of the incoming record.

The problem

is that I am using date_part('week', created_date)::text to get the number of the week, it works just fine 99% of the time, but when the created_date is in the start of January, for example 02.01.2021, date_part('week', created_date)::text returning number 53, when logically it's obviously the first week of the year, maybe you can suggest me how to get the proper week number for the 100% of the year?

Answer 1

From the docs:

The number of the week of the year that the day is in. By definition (ISO 8601), the first week of a year contains January 4 of that year. (The ISO-8601 week starts on Monday.) In other words, the first Thursday of a year is in week 1 of that year.

Because of this, it is possible for early January dates to be part of the 52nd or 53rd week of the previous year. For example, 2005-01-01 is part of the 53rd week of year 2004, and 2006-01-01 is part of the 52nd week of year 2005.

So, it's quite normal, that January 1st does not always lies in the first week. So, 53 is correct here because it means the 53rd week of the previous year.

---

Unfortunately there is nothing like isoweek for date_part() like there is isoyear for year. week is always the ISO week. But you can use this workaround:

demo:db<>fiddle

For converting a date into a string using a certain pattern, you can use ww and iw - week and ISO week:

SELECT to_char('2021-01-02'::date, 'iyyy-iw');

returns 2020-53, the ISO values. Whereas

SELECT to_char('2021-01-02'::date, 'yyyy-ww');

returns 2021-01, which is what you are expecting.

So you can use to_char to get your week number and than cast it into an int:

SELECT to_char('2021-01-02'::date, 'ww')::int;