Add months in db date using interval in mysql

Question

I want to add month in transaction date using mysql interval function by join plan table and transaction table,however this method not working but If I add months in static way to transaction date it is working.

plan table:

plan_id    plan
1         6 month    
2         12 month    
3         3 month

transaction table:

id  user_id  subscribed_on   plan_id    
1     2       2020-04-04     1    
2     4       2019-02-22     2 

Mysql query (not working):

SELECT t.* FROM transaction t inner join plan p on p.plan_id=t.plan_id 
where t.user_id=2 and DATE_ADD(date(t.subscribed_on), INTERVAL p.plan) >= CURDATE() 
order by t.id desc

If I add month in static way than it is working fine:

SELECT t.* FROM transaction t inner join plan p on p.plan_id=t.plan_id 
where t.user_id=2 and DATE_ADD(date(t.subscribed_on),
INTERVAL 6 month) >= CURDATE() 
order by t.id desc

Answer 1

MySQL does not support using interval that way. Unlike in other databaes (such as Postgres for example), the unit argument is a keyword, not a literal string.

I would suspect that your table may store other intervals than just months (say, years, days, and so on). If so, you can use string functions and a case expression to accommodate the different possible values, like:

select t.* 
from transaction t 
inner join plan p on p.plan_id = t.plan_id 
where 
    t.user_id = 2 
    and date(t.subscribed_on) + case substring_index(p.plan, ' ', -1)
        when 'year'  then interval substring_index(p.plan, ' ', 1) year
        when 'month' then interval substring_index(p.plan, ' ', 1) month
        when 'day'   then interval substring_index(p.plan, ' ', 1) day
    end
    >= current_date
order by t.id desc

The logic here is to split the stored interval string into two parts: the number, and the unit; the case expression processes the unit and generate the proper literal interval accordingly.