使用DATE_ADD和列名作为间隔值 [英] Using DATE_ADD with a Column Name as the Interval Value

问题描述

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

I have a table which contains products, a start date and an interval value :

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，以便我的数据集以编程方式返回开始日期和结束日期.

I'm trying to add a calculated value in my select statement to add the interval to the start_date so that my dataset returns with the start and end date programmatically.

这是我现在拥有的:

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

但是，它在DATE_ADD行上返回错误(错误的SQL语法).

However, it's returning an error against the DATE_ADD line (incorrect SQL Syntax).

我读过的所有SO文章似乎都表明表达式和类型需要分开(即DATE_ADD(start_date, INTERVAL expiry_value expiry_type))-确实不是这种情况，我可以将一个句点一一传递场?

All of the SO articles that I've read seem to indicate that the expression and the type need to be separate (i.e. DATE_ADD(start_date, INTERVAL expiry_value expiry_type)) - Surely this isn't the case and I can just pass a period in one single field?

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

If not and since I can't change the data schema, what would be the recommended way of doing it? I contemplated using SUBSTRING_INDEX to split the column by but this doesn't appear to work either.

推荐答案

要获得结果并不容易-因为您将其存储为普通的1 Year或类似名称. 不允许在INTERVAL构造中动态使用它-MySQL语法

It won't be easy o get your result - since you're storing it as plain 1 Year or similar. It is not allowed to use it dynamically in INTERVAL construct - MySQL syntax demands that you'll point both interval quantity and type.

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

However, there's kind of trick to resolve the matter:

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

-如您所见，此查询依赖于事实，数量始终排在第一位(因此 CASE 子句

-as you can see, this query relies on fact, that quantity always goes first (so CAST will extract exactly it, therefore, it can be used to get interval length after this). But in any case, you'll have to recount all possible interval types in CASE clause

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

Another good idea would be - to store your period in unified form (for example, always in days) - so you'll store only one number for each row (thus, 1 week=7days, e t.c.)

这篇关于使用DATE_ADD和列名作为间隔值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！