Interpret MySQL column as a date interval unit?

user_1170839 0
Viewed 234

When using Date and Time functions in MySQL, like DATE_ADD, is it possible to store the INTERVAL unit in a column and use it in a query?

For instance, consider the following table:

+---------------------------+---------------------------------------------------+------+-----+---------+----------------+
| Field                     | Type                                              | Null | Key | Default | Extra          |
+---------------------------+---------------------------------------------------+------+-----+---------+----------------+
| snapshotSchedule_entry_id | int(10) unsigned                                  | NO   | PRI | NULL    | auto_increment |
| snapshotSchedule_id       | int(10) unsigned                                  | NO   | PRI | NULL    |                |
| everyFrequency            | smallint(5) unsigned                              | NO   |     | NULL    |                |
| everyInterval             | enum('minute','hour','day','week','month','year') | NO   |     | NULL    |                |
| afterFrequency            | smallint(5) unsigned                              | NO   |     | NULL    |                |
| afterInterval             | enum('minute','hour','day','week','month','year') | NO   |     | NULL    |                |
+---------------------------+---------------------------------------------------+------+-----+---------+----------------+

The following query works fine, because it is only dealing with the INTERVAL expression:

select * from snapshotSchedule_entry order by date_add(now(), interval everyFrequency month);

When I want to deal with the interval unit (e.g. month, year, etc), it does not interpret it.

mysql> select * from snapshotSchedule_entry order by date_add(now(), interval 1 everyInterval);ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'everyInterval)' at line 1

I'm not surprised by this behavior, I'm just curious if it's possible to make this work. It's not the end of the world to do this in code, but I wonder if it's possible.

select sql-order-by date mysql sql
1 Answers

MySQL is not that flexible with interval expressions. You would need a big case expression with hardcoded values:

order by case everyInterval
    when  'minute' then now() + inteval everyFrequency minute
    when  'hour'   then now() + inteval everyFrequency hour
    when  'day'    then now() + inteval everyFrequency day
    when  'week'   then now() + inteval everyFrequency week
    when  'month'  then now() + inteval everyFrequency month
    when  'year'   then now() + inteval everyFrequency year
end

A simpler alternative would be to redesign your schema to store the interval with fixed frequencies, say seconds and months. You could then do:

order by now() 
    + interval everyFrequencySecond second
    + interval everyFrequencyMonth  month
user_106767160
Related