Group time series by day

[Elliott Balsley]

Is there a way to do something like GROUP BY in PromQL?  My use case is to convert Watts to Watt-Hours.  I have a gauge metric measuring instantaneous power consumption every minute.  I want to calculate the daily consumption to display in Grafana as a time series with one bar per day over a 30 day period.

I found a thread here describing how to do this with Influx, but I can't figure out how to do this in Prometheus.  I've tried a bunch of variations of sum_over_time and avg_over_time. https://community.grafana.com/t/calculate-energy-consumsion-manually/45739/2

Alternatively, my energy monitor device also records Watt-hours as a counter metric which resets every day.  I'm not sure if that would make this any easier...

[Bryan Boreham]

Off the top of my head it seems this should be: avg_over_time(watts[24h]) * 24

If you tried this please say what went wrong.

An alternative would be: sum_over_time(watts[24h:1m]) / 60.

This uses a subquery to produce a value every 1 minute, adds them all up to give a total in Watt-minutes, then divides to get Watt-hours.

Using the counter: increase(watt_hours_total[24h]).

Set the Step in Grafana to 24h to get one point per day.

Set an "Absolute time range" with From=now-31d/d and To=now-1d/d to get 30 days ending midnight yesterday.

Bryan

[Elliott Balsley]

Wow!  Both of these methods work perfectly, with Grafana step set to 24h.  It was initially hard for me to wrap my head around this but after thinking about your examples it makes sense now.  Thanks Bryan!

One more piece that's not working:  Even with the time ranges set as you described (from: now-31d/d, to: now-1d/d), it looks like each measurement is taken at the current time instead of midnight. Response data looks like this, at 14:27 local time.

"Time","Spa"

2024-12-24 14:27:14,5.15 kWh

2024-12-25 14:27:14,7.62 kWh

2024-12-26 14:27:14,7.41 kWh

Grafana’s time picker says midnight as shown in this screenshot.

[Jseb Tarot]

Energie is a counter it is not possible to calculate! from power! Instant value!. See solution from you exporter value. It is problaly existe natively this metric on your device!

--
You received this message because you are subscribed to the Google Groups "Prometheus Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to prometheus-use...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/prometheus-users/40ac1755-6e5a-4662-a2df-dc3c51eb8745n%40googlegroups.com.

[Bryan Boreham]

Good to hear some of it worked.

I don't know how to make the time midnight in Grafana.  Probably better asking in a Grafana forum.

You can use the Grafana 'query inspector' to see what the front-end is sending; you'd need to turn on query-logging in Prometheus to see exactly what is sent by the Grafana back-end.

Bryan