Using DATE_ADD with a Column Name as the Interval

问题:

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

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.

Here's what I have at the moment:

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

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

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?

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:

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

-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

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.)

回答2:

You need to test for each one individually:

select product_name, start_date, expiry_period,
       (case when expiry_period like '%day'
             then DATE_ADD(start_date, INTERVAL expiry_period + 0 DAY) as end_date
             when expiry_period like '%week'
             then DATE_ADD(start_date, INTERVAL expiry_period + 0 WEEK) as end_date
             when expiry_period like '%month'
             then DATE_ADD(start_date, INTERVAL expiry_period + 0 MONTH) as end_date
             when expiry_period like '%year'
             then DATE_ADD(start_date, INTERVAL expiry_period + 0 YEAR) as end_date
       end)
from tbl_products;

The arithmetic (+ 0 and * 7) converts the string to a number.