Hey reader, long time lurker first time poster. Hoping someone with wisdom beyond my own can guide a young padawan.
The version of Grafana I am using is: Grafana v11.3.1 (9225f4a1cb)
I am trying to turn a cumulative totalizer into a daily resettable totalizer (see images). The first is what I currently have which is the totalizer based to zero. The second is what I am aiming to get the data to look like (Saw-Tooth).
I understand what the query should look like and I have been able to zero base my data, but the view sums from 0 for as long as the time frame goes. I want to have a 7 day or whatever look back sum from 0 at midnight each night to whatever value the meter pulls up to for the day.
If anyone has solution or advice on getting this solution any advice would be help!
Sincerely,
A ReadOnly Viewer
What Grafana version and what operating system are you using?
What are you trying to achieve?
How are you trying to achieve it?
What happened?
What did you expect to happen?
Can you copy/paste the configuration(s) that you are having problems with?
Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
Did you follow any online instructions? If so, what is the URL?
Good day, We are trying to do same thing here at our site. In our PLC, some have ‘daily resettable’ that zero out at midnight, but most of data points only have totalizer (non-resetting) or realtime rates that we convert to totals in grafana.
All of those NR and rates are already in grafana, so it would be a lot of work for us to add in PLC daily resets for every one. Would really like to see if this is possible in grafana.
If your totalizer data is in a SQL datasource (PostgreSQL, MySQL, TimescaleDB, SQL Server), you can do this entirely in the query → no PLC changes or Grafana transformations are required.
SELECT
time,
value - MIN(value) OVER (PARTITION BY DATE(time)) AS daily_value
FROM your_table
WHERE $__timeFilter(time)
ORDER BY time;
PARTITION BY DATE(time) creates a separate partition for each calendar day. For a monotonically increasing totalizer, the day’s minimum value corresponds to the first reading of that day. Subtracting that value from each cumulative reading produces the desired saw-tooth pattern: the value starts at 0 each day, increases throughout the day, and resets at the next midnight.
If your timestamps are stored in UTC but you want the reset to occur at local midnight, partition using your local timezone instead, for example:
DATE(time AT TIME ZONE 'America/Chicago')
(replace America/Chicago with your own timezone).
This approach assumes the counter only increases during the day. If the counter can reset or roll over mid-day (for example, after a PLC reboot), then using the day’s minimum value is no longer sufficient, and a different query is needed to detect those resets.
If you’re using InfluxDB/Flux instead of SQL, the same concept applies, but the implementation is different.
Yes, but I’d move the cumulative-sum logic from the Grafana transformation into the PostgreSQL query.
If your rate column already represents the amount for each sample (rather than an instantaneous rate), you can use a running sum:
SELECT
time,
SUM(rate) OVER (PARTITION BY DATE(time) ORDER BY time) AS daily_value
FROM your_rate_table
WHERE $__timeFilter(time)
ORDER BY time;
PARTITION BY DATE(time) resets the running total each day, while ORDER BY time accumulates it row by row. In this case, remove the Grafana Cumulative sum transformation, since the query already produces the cumulative values.
If your column contains a true flow rate (for example, L/s, gal/min, or m³/h), then the rate first needs to be integrated over time before applying the running sum. The exact SQL depends on how the rate is sampled (regular vs irregular intervals) and the units of the rate.
We are using influx/flux db. How can we modify query to use?
I have also been resample the data to 1m interval since our rate is gallon per min and the data does not recorded exactly at 1m interval. If we sum without resample then we will get too high result.
Since your values are gallons per minute, they’re true rates rather than cumulative values, so resampling before accumulating is the right approach. Otherwise, treating each sample equally despite irregular sampling intervals can overestimate the total.
In Flux, the equivalent of the PostgreSQL approach is →
Resample the rate to a fixed interval (which you’re already doing with aggregateWindow(every: 1m, ...)).
Convert the resampled data into a cumulative total.
Reset that cumulative total at each day boundary.
The exact Flux query depends on your current pipeline (especially how you’re resampling and what aggregation function you’re using), so could you share your current Flux query? That will make it possible to show the minimal change needed to produce the daily saw-tooth.
I think the key issue is that the resampling is happening in a Grafana Expression, not in the datasource query. Because of that, the datasource never sees the 1-minute resampled series, so it can’t produce a daily resetting cumulative total from that resampled data.
Also, the query you posted is InfluxQL, not Flux. If you’re able to switch to Flux, you can move the resampling, cumulative calculation, and daily reset into the query itself.
