Sum over time of 1d, limit to last 7d, counter as a value

Question

Let's suppose I have metric purchases_total. It's a counter ( which constantly increases ). I would like to make a table in Grafana which:

• Shows the last 7 days
• Sums over 1d

I try to make this query, but it returns nonsense:

sum_over_time(sum(increase(purchases_total{deviceType='ios'}[2m])/2)[7d:1d])

P.S 2m it's a scraping interval. Also, I put a "Min step" of 1d into a query configuration ( between the legend input field and resolution input field ) to limit a table view ( in Grafana ).

Any advice will be highly appreciated! Thanks

Answer 1

Your question isn't 100% clear, but I think you are looking for sum(increase(purchases_total{deviceType='ios'}[1d]) and then use a 1d step to the query_range API with start/end covering 7 days.

Answer 2

The following PromQL query should return the number of purchases for the previous day:

last_over_time(
  increase(purchases_total{deviceType='ios'}[1d])[1d:1d]
)

It uses subquery feature over last_over_time and increase functions. The outer last_over_time(...[1d:1d]) is needed in order to align calculations to boundaries between days in UTC time zone.

The query returns results shifted by one day in the past. This can be fixed by adding offset -1d to the query:

last_over_time(
  increase(purchases_total{deviceType='ios'}[1d] offset -1d)[1d:1d]
)

Note also that Prometheus may return fractional results from increase() over integer counters because of extrapolation. See this issue for details. Additionally, Prometheus ignores the difference between the last sample on the previous day and the first sample on the current day when calculating increase(). This may lead to incorrect results for slow-changing counters. See this comment and this article for details. Both issues should be addressed soon by Prometheus developers according to this design doc.

In the mean time it is possible to use VictoriaMetrics, which provides increase() function without these issues.