Create a query using an enum I have a table where one of the columns is an enum. 

mysql&gt; SELECT COLUMN_TYPE FROM information_schema.columns WHERE [...];
+--------------------------------------------------------------+
| COLUMN_TYPE                                                  |
+--------------------------------------------------------------+
| enum('1 DAY','1 WEEK','1 MONTH','3 MONTH','1 YEAR','2 YEAR') |
+--------------------------------------------------------------+
1 row in set (0.00 sec)


I want to use these values in a subsequent query, something like this:

mysql&gt; SELECT LPAD(MONTH(DATE_SUB(NOW(), INTERVAL 1 DAY)), 2, 0) A, 
              LPAD(MONTH(DATE_SUB(NOW(), INTERVAL 1 WEEK)), 2, 0) B, 
              LPAD(MONTH(DATE_SUB(NOW(), INTERVAL 1 MONTH)), 2, 0) C, 
              LPAD(MONTH(DATE_SUB(NOW(), INTERVAL 1 YEAR)), 2, 0) D, 
              LPAD(MONTH(DATE_SUB(NOW(), INTERVAL 2 YEAR)), 2, 0) E, 
              YEAR(DATE_SUB(NOW(), INTERVAL 1 DAY)) F, 
              YEAR(DATE_SUB(NOW(), INTERVAL 1 WEEK)) G, 
              YEAR (DATE_SUB(NOW(), INTERVAL 1 MONTH)) H, 
              YEAR(DATE_SUB(NOW(), INTERVAL 1 YEAR)) I, 
              YEAR(DATE_SUB(NOW(), INTERVAL 2 YEAR)) J\G
*************************** 1. row ***************************
A: 05
B: 05
C: 04
D: 05
E: 05
F: 2018
G: 2018
H: 2018
I: 2017
J: 2016
1 row in set (0.00 sec)


I run these queries from a script and parsing the result of the first query and creating the second query isn't a massive problem. I was just wondering if someone could suggest a way to do this in one go.
There is no way to do what you want. ENUMs are inherently a unique textual representation, stored in a special way. You can't add a dynamic to them that makes them useful in any sense to an INTERVAL. 


MySQL doesn't really have ENUM types, it has more of a macro over intergers (a hack). MDEV-4912, MDEV-4913
MySQL also lacks an interval type


For these reasons, the best you can do is probably a lookup table.

See also,


https://dba.stackexchange.com/a/177075/2639