MySQL - Subtrack week from YearWeek column correctly-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

Utilising what you have perhaps a sub query to find the most recent prior to current

with cte as
(
select yearweek(dte) yw, count(*) 
from dates 
where dte between'2021-12-15' and '2022-01-15'
group by yearweek(dte)
)
select *,(select yw from cte cte1 where cte1.yw < cte.yw order by cte1.yw desc limit 1) prevyw
from cte
;

 -------- ---------- -------- 
| yw     | count(*) | prevyw |
 -------- ---------- -------- 
| 202150 |        4 |   NULL |
| 202151 |        7 | 202150 |
| 202152 |        7 | 202151 |
| 202201 |        7 | 202152 |
| 202202 |        7 | 202201 |
 -------- ---------- -------- 
5 rows in set (0.006 sec)