Write query to get entire currently selected month

  • What Grafana version and what operating system are you using?

Ubuntu 20.04, grafana 8.5.0, MySQL DB

  • What are you trying to achieve?

I want a Stat panel, that returns the summed value of the currently selected month.
This is the hardcoded version:

SELECT
UNIX_TIMESTAMP(TS) AS “time”,
P_Plus/4 AS “August”
FROM $meterID
WHERE
TS BETWEEN ‘2022-08-01 00:01:00’ AND ‘2022-09-01 00:00:00’
ORDER BY TS

So i need to a way to replace the hardcoded values to the entire month of the selected “From”-Range

TS BETWEEN AND <end of selected month)

So if I select 2022-08-03 12:32:32 I want to see the values of the entire month of August

  • How are you trying to achieve it?

I tried this:

TS between DATE_SUB( ${__from:date}, INTERVAL DAYOFMONTH( ${__from:date}) - 1 DAY) and DATE_SUB( ${__from:date}, INTERVAL DAYOFMONTH( ${__from:date}) - 30 DAY)

  • What happened?

I got an error, that the synatx is not correct. There is also the issue, that this forumula above would always expect the month to have exact 30 Days

  • Did you follow any online instructions? If so, what is the URL?

I read about time ranges here, but i still can’t find the solution.

Any ideas?