MySQL CASE with user parameter in WHERE clause

wyatt
March 8, 2024
164 views
1 vote
2 Answers

I am trying to obtain all days to a month in my MySQL database.
This query is being called in one of my Grafana dashboards that has a variable for the year, the month and the day. This query is supposed to get all the days there are to the selected month. The user can select a written month, but the months in my table are numbers. Since Grafana doesn’t seem to allow you to value map the variables, I’ll have to do that in my SQL query. Sadly the query doesn’t seem to work and only returns this error:
[42S22][1054] Unknown column 'January' in 'where clause'
Of course the month depends on what the user has chosen.
This is my SQL query:

SELECT DISTINCT DAY(measurement_timestamp) AS timestamp
FROM my_table
WHERE EXTRACT(YEAR FROM measurement_timestamp) = ${Year}
  AND EXTRACT(MONTH FROM measurement_timestamp) = CASE
                                                      WHEN ${Month} = 'January' THEN 1
                                                      WHEN ${Month} = 'February' THEN 2
                                                      WHEN ${Month} = 'March' THEN 3
                                                      WHEN ${Month} = 'April' THEN 4
                                                      WHEN ${Month} = 'May' THEN 5
                                                      WHEN ${Month} = 'June' THEN 6
                                                      WHEN ${Month} = 'July' THEN 7
                                                      WHEN ${Month} = 'August' THEN 8
                                                      WHEN ${Month} = 'September' THEN 9
                                                      WHEN ${Month} = 'October' THEN 10
                                                      WHEN ${Month} = 'November' THEN 11
                                                      WHEN ${Month} = 'December' THEN 12
    END
ORDER BY timestamp DESC;

Answers

mysql supports monthname

So your query would look more like

SELECT DISTINCT DAY(measurement_timestamp) AS timestamp
FROM my_table
WHERE EXTRACT(YEAR FROM measurement_timestamp) = ${Year}
  AND MONTHNAME( measurement_timestamp) = ${Month} 
ORDER BY timestamp DESC;

If you need the number you need to enclose the ${month} in single quotes, so that MySQL interprets the monthname as string and not as column name

SELECT DISTINCT DAY(measurement_timestamp) AS timestamp
FROM my_table
WHERE EXTRACT(YEAR FROM measurement_timestamp) = ${Year}
  AND EXTRACT(MONTH FROM measurement_timestamp) = CASE
                                                      WHEN '${Month}' = 'January' THEN 1
                                                      WHEN '${Month}' = 'February' THEN 2
                                                      WHEN '${Month}' = 'March' THEN 3
                                                      WHEN '${Month}' = 'April' THEN 4
                                                      WHEN '${Month}' = 'May' THEN 5
                                                      WHEN '${Month}' = 'June' THEN 6
                                                      WHEN '${Month}' = 'July' THEN 7
                                                      WHEN '${Month}' = 'August' THEN 8
                                                      WHEN '${Month}' = 'September' THEN 9
                                                      WHEN '${Month}' = 'October' THEN 10
                                                      WHEN '${Month}' = 'November' THEN 11
                                                      WHEN '${Month}' = 'December' THEN 12
    END
ORDER BY timestamp DESC;

I’d be inclined to approach this slightly differently, especially if you have an index on measurement_timestamp. If you convert your {Year} and {Month} variables to a date, you can then use a date range in your WHERE which would be sargable. Converting your month and year to a date can be done with STR_TO_DATE AND DATE_FORMAT, so the below would create a date variable of 2024-03-01 from separate month and year inputs:

SET @Month ='March';
SET @Year = '2024';
SET @Date = DATE_FORMAT(STR_TO_DATE(CONCAT_WS(' ','01', @Month, @Year), '%d %M %Y'),'%Y-%m-%d');

You can then put this in your query, e.g.

WHERE measurement_timestamp >= @Date
AND measurement_timestamp < @Date + INTERVAL 1 MONTH;

If you want to avoid variables and also avoid repeating your conversion, you could put this in a derived query, ending up with something like:

SELECT DISTINCT DAY(t.measurement_timestamp) AS timestamp
FROM my_table AS t
CROSS JOIN (SELECT DATE_FORMAT(STR_TO_DATE(CONCAT_WS(' ','01', '{Month}', '{Year}'), '%d %M %Y'),'%Y-%m-%d') AS DateStart) AS d
WHERE t.measurement_timestamp >= d.DateStart
AND t.measurement_timestamp< d.DateStart +INTERVAL 1 MONTH;

Simplified example on db<>fiddle

Alternatively you can do the same thing (i.e. convert the incoming parameters to a date rather than functions on the date column) with MAKEDATE():

SELECT DISTINCT DAY(t.measurement_timestamp) AS timestamp
FROM my_table AS t
CROSS JOIN (SELECT MAKEDATE({Year}, 1)
                + INTERVAL 
                CASE
                    WHEN '${Month}' = 'January' THEN 0
                    WHEN '${Month}' = 'February' THEN 1
                    WHEN '${Month}' = 'March' THEN 2
                    WHEN '${Month}' = 'April' THEN 3
                    WHEN '${Month}' = 'May' THEN 4
                    WHEN '${Month}' = 'June' THEN 5
                    WHEN '${Month}' = 'July' THEN 6
                    WHEN '${Month}' = 'August' THEN 7
                    WHEN '${Month}' = 'September' THEN 8
                    WHEN '${Month}' = 'October' THEN 9
                    WHEN '${Month}' = 'November' THEN 10
                    WHEN '${Month}' = 'December' THEN 11
                END MONTH AS DateStart) AS d
WHERE t.measurement_timestamp >= d.DateStart
AND t.measurement_timestamp< d.DateStart +INTERVAL 1 MONTH;

Further example on db<>fiddle

Please signup or login to give your own answer.

Click here to cancel reply.