datetime - How to use a string/column value as a mysql date interval constant (DAY, MONTH...)?

Question

I have three columns: a date column, a integer column, and a varchar column like this:

+------------+------+---------+
| date       |value |  unit   |
+------------+------+---------+
| 2009-01-01 |    2 | DAY     | 
| 2009-02-01 |    3 | MONTH   | 
+------------+------+---------+

I want to use the values of the integer and the varchar column in a mysql date_add() function as part of the "INTVERAL" expression, added to the date in the 'date' column.

For example: date_add(2009-01-01, INTERVAL 2 DAY), so that the '2009-01-01' is from the 'date' column, the '2' is from the "value"/integer column and the "DAY" is from the 'unit'/varchar column.

And the select would look like this:

select date_add(table.date_column, INTERVAL table.integer_column table.varchar_column) from table

Problem is: it doesn't work. The date and the integer column work, so this is ok:

 select date_add(table.date_column, INTERVAL table.integer_column DAY) from table

but as soon I try to replace the "DAY" Keyword with a string value from a column I get an error message.

Any Ideas?

I guess more generally the problem is:

How do I use dynamically retrieved values as constants/key expressions? Is there a kind of "eval" function in mysql ?

This problem has been bugging me for a long time now, any help would be really great.

Beat

Answer 1

Unfortunately MySQL expects a keyword after INTERVAL and not any string or numeric value. You can achieve what you want by using a CASE statement and give the different cases with the different keywords.

As an example, let's say you want to add the value with the appropriate unit to the date then the SQL statement would be as follows:

SELECT CASE unit
       WHEN "DAY" THEN date_add(date, INTERVAL value DAY)
       WHEN "MONTH" THEN date_add(date, INTERVAL value MONTH)
       END
       AS newDate
FROM table

Also works in the WHERE clause by the way :)