CODEWITHSUNDEEP
mysqlsqldatetimeintervals
msp
msp

Reputation: 127

Add months in db date using interval in mysql

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

Upvotes: 0

Views: 993

Answers (3)

marcothesane
marcothesane

Reputation: 6721

Try to force the plan column in the plan table to be an integer. Does not seem to be possible to cast a string to an interval.

I tried like so:

WITH
plan( plan_id,plan) AS (
          SELECT 1,'6 month'
UNION ALL SELECT 2,'12 month'    
UNION ALL SELECT 3,'3 month'
)
,
transaction(id,user_id,subscribed_on,plan_id) AS (
          SELECT 1,2,DATE '2020-09-04',1    
UNION ALL SELECT 2,4,DATE '2019-02-22',2 
)
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 CAST(REPLACE(plan,' month','') AS SIGNED) MONTH
    ) >= CURDATE()
ORDER BY t.id DESC

(returns no results, as you don't have any dates high enough in your example data...)

Upvotes: 1

GMB
GMB

Reputation: 222402

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Unfortunately a string in the data is not equivalent to an interval. One method is:

date(t.subscribed_on) + interval substring_index(plan, ' ') + 0 month

Note here that month is a keyword, not a string.

Upvotes: 1

Related Questions