使用DATE_ADD和列名作为间隔值

一尘不染

使用DATE_ADD和列名作为间隔值

sql

我有一个表，其中包含产品，开始日期和时间间隔值：

product_name                    start_date              expiry_period
Domain Registration (1 Year)    2013-12-08 00:00:00     1 Year
Domain Registration (1 Year)    2013-12-01 00:00:00     1 Year
Website Hosting (Bronze)        2013-12-19 00:00:00     1 Year
Website Hosting (Silver)        2013-12-20 00:00:00     1 Year
Website Hosting (Silver)        2013-12-21 00:00:00     1 Year
Domain Registration (2 years)   2014-01-04 00:00:00     2 Year
Domain Registration (1 Year)    2014-01-04 00:00:00     1 Year
Website Hosting (Silver)        2014-01-06 00:00:00     1 Year
Domain Registration (2 years)   2014-01-06 00:00:00     2 Year
Domain Registration (1 Year)    2014-01-07 00:00:00     1 Year
Domain Registration (1 Year)    2014-01-10 00:00:00     1 Year
Website Hosting (Bronze)        2014-01-12 00:00:00     1 Year

我试图在我的select语句中添加一个计算值，以将间隔添加到start_date，以便我的数据集以编程方式返回开始日期和结束日期。

这是我目前所拥有的：

select 
    product_name, 
    start_date,
    expiry_period
    DATE_ADD(start_date, INTERVAL expiry_period) as end_date
from
    tbl_products

但是，它针对DATE_ADD行返回错误（错误的SQL语法）。

我读过的所有SO文章似乎都表示表达式和类型需要分开（即DATE_ADD(start_date, INTERVAL expiry_value expiry_type)）-确实不是这种情况，我只能在一个字段中输入一个句点？

如果不能，并且由于我无法更改数据模式，推荐的处理方式是什么？我考虑过使用SUBSTRING_INDEX分割列，但这似乎也不起作用。

阅读 165

2021-05-05

共1个答案

一尘不染

获得您的结果并非易事-因为您将其存储为纯文本1 Year或类似文本。这是不容许在动态地使用它INTERVAL构造-
MySQL的语法要求，你会点这两个区间数量和类型。

但是，有一种技巧可以解决此问题：

SELECT 
    product_name, 
    start_date,
    expiry_period,
    @num:=CAST(expiry_period AS UNSIGNED),
    @p  :=SUBSTR(expiry_period, CHAR_LENGTH(@num)+2),
    CASE
      WHEN @p='Year' THEN DATE_ADD(start_date, INTERVAL @num YEAR)
      WHEN @p='Month' THEN DATE_ADD(start_date, INTERVAL @num MONTH)
      WHEN @p='Day' THEN DATE_ADD(start_date, INTERVAL @num DAY)
      WHEN @p='Week' THEN DATE_ADD(start_date, INTERVAL @num WEEK)
    END AS end_date
FROM
    tbl_products

-如您所见，此查询依赖于事实，该数量始终排在第一位（因此CAST将精确提取该数量，因此，此后它可用于获取间隔长度）。但是无论如何，您都必须在子句中重新计算 所有可能的 间隔类型CASE

另一个好主意是-以统一的形式存储您的期间（例如，始终以天为单位）-因此，每行只存储一个数字（因此，1周= 7天，等等）

2021-05-05