There is time series with counter status (gas meter). How to calculate consumption for a certain period?
1 Like
Welcome @fjanni
You might want to check out PromQL as a query language, that lets you work with these kinds of metrics and do all kinds of cool computations. In your particular case, you should check out the delta() PromQL query
Here’s an example screenshot of what that looks like in explore.
You’ll notice I used 2h (2 hours) as the example time window – but you can do any time window you want by using different time durations.
Thank you but I’m not familiar with Prometheus and PromQl. I use Grafana and data source is MySQL.
Could you please explain how to setup it?
1 Like
If you have Grafana, then you already have Prometheus and PromQL, it’s baked in, so there’s nothing to set up. But honestly you won’t need those, you didn’t mention MySQL before.
Broadly speaking, what you want to do is:
- Ensure you have the MySQL plugin already in your Grafana.
- Create a data source for MySQL that knows how to connect to your instance (username, password, hostname)
- Use the “Explore” button to create a query against the data source. You can use SQL
In the MySQL case, probably the easiest is just to write the appropriate query for elapsed, it would be something like (but not exactly this)
SELECT max(Counter)-min(Counter) AS consumption FROM table
WHERE Time >= (beginning of range) AND Time <= (end of range)
When you get your query right, you could set up a panel to display the results of that query in Grafana
This is the panel
and this is the query
SELECT
Zeit AS “Time”,
Zaehlerstand AS “Counter”
FROM emspressigstrom.
131_uvx2_3_zentralentstaubungIf I put max() and min()
max (Zaehlerstand)-min(Zaehlerstand) as “Consumption”
I get error message:
db query error: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘max (Zaehlerstand)-min(Zaehlerstand) as “Consumption” FROM emspressigstrom.`…’ at line 4
Hi @fjanni
Your initial post had 3 columns (ID, Time and Counter), but your most recent post referenced different column names (Zeit, Zaehlerstand, etc.). Using columns named Time and Counter, perhaps something like this would work to calculate weekly consumption?
SELECT
$__timeGroupAlias(Time, '1w') AS "time",
MAX(Counter) - MIN(Counter) AS WeeklyConsumption
FROM
your_table_name
WHERE
$__timeFilter(Time)
GROUP BY
1
ORDER BY
"time"
This query uses the $__timeGroupAlias macro to group the data by the week, and the $__timeFilter macro to apply a time range filter based on the selected time range in the Grafana dashboard. The GROUP BY 1 groups the results by the first column, which I presume is Time (although maybe it’s Zeit in your data). The query calculates the difference between the maximum and minimum counter values within each week to represent the weekly consumption.
1 Like
Thank you for the explanation.
Now it works like this:
SELECT
Zeit AS “Time”,
max(Zaehlerstand)-min(Zaehlerstand) as “WeeklyConsumption”
FROM emspressigstrom.table
GROUP BY (week(Zeit))
order by ID
But it seems to me that $__time… macro doesn’t work when I replace simple time function with macro I get a syntax error message.
And sometime I’m worned that “db has no time column”
Hi again @fjanni
I believe the first SELECT statement does not need the AS 'time' (this may only be required for Microsoft SQL, but not MySQL). So try shortening to just this:
$__timeGroupAlias(Zeit, '1w'),
I found this example on play.grafana.org to be helpful (the data set only spans 1 week in 2005, but it can be used to experiment with).
Hourly:
Daily:
1 Like