MySQL - Subtrack week from YearWeek column correctly

Question

I have a calendar table in MySQL that maintains a column having YEARWEEK value (eg: 202230, 202301). When I am trying to substract one week from this column (which is integer type), for boundary cases like 202301, it is giving incorrect result.

Eg: 202230 - 1 = 202229
Eg: 202301 - 1 = 202301 which is incorrect. Correct value should be 202252.

I have tried to do cast it into string and do substring operations however few years have 53 weeks.

What is the correct way I can achieve this?

Answer 1

You can get the date of a specific day, for example Sunday, of that yearweek value with STR_TO_DATE():

STR_TO_DATE('202301 Sunday', '%X%V %W')

and subtract 1 week:

STR_TO_DATE('202301 Sunday', '%X%V %W') - INTERVAL 1 WEEK

and the above date can be used to get the yearweek that you want:

YEARWEEK(STR_TO_DATE('202301 Sunday', '%X%V %W') - INTERVAL 1 WEEK)

In the case of your calendar table you can do it like this:

SELECT YEARWEEK(
         STR_TO_DATE(
          CONCAT(year_week_col, ' Sunday'), '%X%V %W') - INTERVAL 1 WEEK
       ) AS result
FROM calendar;

Change calendar and year_week_col to the names of the table and the column respectively.

See the demo.