Добавление месяцев в дату БД с использованием интервала в mysql

#mysql #sql #datetime #intervals

#mysql #sql #дата и время #интервалы

Вопрос:

Я хочу добавить месяц в дату транзакции, используя функцию интервала mysql, путем объединения таблицы плана и таблицы транзакций, однако этот метод не работает, но если я добавляю месяцы статическим способом к дате транзакции, он работает.

plan таблица:

 plan_id    plan
1         6 month    
2         12 month    
3         3 month

transaction таблица:

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

Запрос Mysql (не работает):

 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

Если я добавлю месяц статическим способом, то он будет работать нормально:

 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

Ответ №1:

MySQL не поддерживает использование интервала таким образом. В отличие от других баз данных (например, Postgres), аргумент unit является ключевым словом, а не буквальной строкой.

Я бы заподозрил, что в вашей таблице могут храниться и другие интервалы, а не только месяцы (скажем, годы, дни и так далее). Если это так, вы можете использовать строковые функции и case выражение для размещения различных возможных значений, например:

 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

Логика здесь состоит в том, чтобы разделить сохраненную строку интервала на две части: число и единицу измерения; case выражение обрабатывает единицу измерения и генерирует соответствующий буквальный интервал соответственно.

1. здесь вы упоминаете, что вручную пишете регистр месяц год день, когда я присоединяюсь к таблице плана, это может быть месяц или год, можете ли вы показать мне, присоединившись к запросу.

2. @msp: да. Это то, что делает запрос в моем ответе.

3. Я не могу понять, что делать, потому что, год, месяц, день проходит статически, как я это буду работать?

4. @msp: case выражение просматривает то, что находится внутри таблицы, и соответственно генерирует правильный интервал. Попробуйте выполнить запрос!

Ответ №2:

К сожалению, строка в данных не эквивалентна интервалу. Один из методов:

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

Обратите внимание, что это month ключевое слово, а не строка.

Ответ №3:

Попробуйте заставить plan столбец в plan таблице быть целым числом. Кажется, невозможно привести строку к интервалу.

Я пробовал так:

 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

(не возвращает результатов, так как у вас нет достаточно высоких дат в вашем примере данных …)