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