SQL convert string 3M to 3 month, 1Y to 1 year

Question

I probably made a poor decision years ago where I have been storing intervals in a char(3) column with values such as "1M" or "3M" or "1Y". This helps me store info about recurring invoices. "1M" means the invoice renews every 1 month.

Here is a sample for the database : https://i.stack.imgur.com/9WidX.png

The reason of this poor design is because I calculate the next invoice date through a php function :

function increment_date($date, $increment)
{
    $new_date = new DateTime($date);
    $new_date->add(new DateInterval('P' . $increment));
    return $new_date->format('Y-m-d');
}

so that I can pass it arguments such as "P1M" which was actually very convenient for DateInterval

I now wish I stored them such as "1 month" instead of "1M", because I am stuck when try to run the following dynamic SQL request :

SELECT SUM(invoice_total) prevision_for_current_month
  FROM lf_invoice_amounts a
  JOIN lf_invoices_recurring r
    ON r.invoice_id a.invoice_id
 WHERE (month(recur_next_date) = 5 and year(recur_next_date)= 2020) 
    OR (month(recur_next_date - INTERVAL recur_frequency) = 5 and year(recur_next_date - INTERVAL recur_frequency) = 2020)

The part with month(recur_next_date - INTERVAL recur_frequency) fails and throws an error because it runs such as month(recur_next_date - INTERVAL 1M) which mySQL does not understand, while the following would have been correct : month(recur_next_date - INTERVAL 1 month)

The purpose of this sql request is to estimate all the money that should come in during current month, from invoices that are recurring every month/3 months/year/2 weeks/etc

I cannot refactor the whole code base at this moment. What would be a possible workaround?

TL;DR : How do I transform a column that contains value "1M" into "1 month" so that I can run SQL requests with intervals. (same for "3M" or "1Y" or "1M" or "2W" or "30D" or "31D", etc). Ugly solutions also welcome. I'm currently think about a big nest of replace() maybe like month(recur_next_date - INTERVAL replace(recur_frequency, 'M', ' month')) ?

score 0 · Accepted Answer · answered May 14 '20 at 03:38

Unfortunately converting your recur_frequency into a string like 1 month isn't going to help, as the syntax for intervals requires that unit be one of YEAR, MONTH, DAY etc. i.e. one of a fixed set of specifiers, not a string, so trying to use something like INTERVAL recur_frequency or even INTERVAL recur_frequency recur_period won't work. Without using dynamic SQL to insert the value of recur_frequency into the query I think your best option is probably to store the recurrence frequency into one of 4 columns (e.g. recur_year, recur_month, recur_week, recur_day), then you can use a query such as

curdate() - interval recur_year year
          - interval recur_month month
          - interval recur_week week
          - interval recur_day day

Demo on dbfiddle

SQL convert string 3M to 3 month, 1Y to 1 year

1 Answers1