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

I think you're better off using a CASE statement for each possible value of the unit column, since it is a finite universe. You can choose whether to keep the column as a varchar, or use an integer code instead.

I would discourage use of dynamically executing SQL statements, even when generated from an existing table unless you are 120% careful in restricting the values that get inserted in the table. What would you do if you somehow got the following values in the table? Oops!

+------------+------+------------------------------------+
| date       |value |                 unit               |
+------------+------+------------------------------------+
| 2009-01-01 |    2 | DAY                                | 
| 2009-02-01 |    3 | MONTH                              |
| 2009-03-01 |    4 | DAY) FROM table; DROP TABLE table; | 
+------------+------+------------------------------------+

Answer 2

I'm in the middle of reinstalling MySQL on my machine, so I can't test it, but perhaps concatenating them into a valid string might help, if CONCAT() is allowed here:

SELECT 
   DATE_ADD(
      table.date_column, 
      INTERVAL CONCAT(
         table.integer_column,
         ' ',
         table.varchar_column
      )
   )