php - SQL convert string 3M to 3 month, 1Y to 1 year
问题描述
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.imgur.com/D8oKaV3.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'))
?
解决方案
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
推荐阅读
- awk - 多个 gzip 文件输入和多个 if else 到 awk
- node.js - 将 1 个 Textarea 行分隔为数组以将它们存储在 MongoDB 中
- c# - 单击按钮后如何在数据网格中创建新行
- python - 带有 tz 偏移的日期时间的 strptime 示例
- python - Seaborn 线图 - 仅显示重复 CSV 数据的部分日期
- javascript - MarkLogic 错误实例未使用实例转换器模块定义
- javascript - 使用 Javascript 单击按钮获取随机图像
- python - Pandas 将唯一值作为具有计数的列
- r - R中是否有任何方法可以将以下文本货币格式转换为数字格式?
- python - 使用 cv2.VideoWriter 将 PIL 图像列表转换为视频幻灯片