Show total consumption by day

Hello guys,

I’ve a signal which is a current consumption of my home in watts. I get this value every second.

I already was able to make a graph to show it over time

Also another graph to show total consumption for a particular time frame selected
Screenshot 2021-06-24 at 12.44.00

Now what I’m trying to do is to draw a graph where it shows the consumption for each day (X axis) and show total consumption (Y axis) but sadly I’m having a hard time creating one.

I’m assuming this is possible to do on Grafana and it’s 100% my fault, in any case do you have any input or articles I can read?. Any help will be greatly appreciated

Thank you!

I think your assumption is sadly misguided.

Grafana is strongly biased towards time-series data, and also time-series
representation of it. What I mean by that is that getting any graph to be
displayed with anything other than time on the X-axis is at the very least
difficult.

You can show histograms which of course do not have time on the X-axis, but
this still won’t do what you’re asking for.

On the other hand, I can’t say I understand what you really are trying to
display. You say “daily consumption on the X-axis and total consumption on
the Y-axis”. I assume “total” here refers to some number of days, such as 7
or 28, but I can’t imagine what such a graph would look like, for example if
you had daily consumptions such as:

day 1: 28
day 2: 33
day 3: 31
day 4: 30
day 5: 34
day 6: 29
day 7: 31

Total: 216

So, where would you expect the data points to be on a graph with daily
consumption on the X-axis and total consumption on the Y-axis?

Antony.

thanks for your quick reply!

This is more or less what I want to do, regardless of what time frame is selected at the top

Ah, so you want days on the X-axis and daily consumption on the Y-axis.

In that case you can set the time selection just for that panel, independently
of the overall dashboard selector.

See Time range controls | Grafana Labs
and Queries | Grafana Labs

Antony.

Thanks so much, I will play with these and see if I can make it work!

This thread may also help - especially if you have irregular power readings Calculate Energy Consumsion manually

2 Likes

I’m doing some progress but I can’t figure out how to display the correct amount per day. I’ve tried a lot of different options on SELECT but no luck. It might be more complicated than what I thought

oh this is nice, I will check it out tomorrow… tons of useful information, thanks!

I’ve tried re-using your code from the other post as

SELECT sum("mean")/3600 FROM (
  SELECT mean("value") FROM "autogen"."shelly_usage_watts" WHERE $timeFilter GROUP BY time(1s) fill(previous)
) WHERE $timeFilter GROUP BY time(1h) fill(null)

But I’m not getting any results but also no errors O_O


I think in this last attempt you’re querying a non-existent measurement.

If I’m not mistaken, based on the screenshot you shared of your original query, your measurement is called "W" and you have a tag filter for entity_id='shelly_usage_watts'.

But in SELECT mean("value") FROM "autogen"."shelly_usage_watts" you’re trying to query data from the measurement shelly_usage_watts, which I assume doesn’t exist, so you get nothing back.

I think right query to get hourly consumption would therefore be

SELECT sum("mean")/3600 FROM (
  SELECT mean("value") FROM "W" WHERE entity_id='shelly_usage_watts' AND $timeFilter GROUP BY time(1s) fill(previous)
) WHERE $timeFilter GROUP BY time(1h) fill(null)

But that’s kind of a guess. If you can show us what some of your raw data looks like (e.g. how often readings are taken, what units they’re in, etc) it might be easier to give specific advice.

Thanks so much @svetb.

My measurements are in Watts every 1 second. What I’m trying to achieve is to show the consumption per day (so each bar will be 1 day of total consumption in kWh) for a defined last X days, let’s say the last 30 days or 14 days… independently from what I have selected in the time range selector at the top of the page

BTW, your query works like a charm, showing hourly data in the graph which is also a nice to have btw

Using your code I kind of got it! Just changed the 1h to 24h

The only thing I’m missing is that I want to show, say the last 30 days but without having to select “Last 30 days” on top of the time range selector, but this is already so cool!

Screenshot 2021-06-25 at 12.15.43

Ok, cool that it’s working!

By the way if you reliably get readings every second you can simplify the query quite a bit (i.e. you don’t need to use a subquery), but I’ll leave that as an “exercise for the reader”.

I’m not quite sure what you mean by this. The whole point of the time picker is that you use it to select the time period. Are you saying that you’d like to set the time period in a different way? Maybe you can describe what you have in mind?

Well it depends on the sensor I look at. The one from shelly doesn’t have readings every second but I’ve created another one using REST api through Home Assistant and that one has readings every second, so actually it’s handy to have it

As for the last 30 day, currently if I select on the time range 24 hours for example, the graphics will update with that timeframe (see image below)

And if I change it to Last 30 days it will show that. (see below)

However for those per day consupmtion graphics I don’t want them to change based on what’s selected, I just want to display the last X days, no matter what’s the time range at the top

Ok got you. Yeah you can apply a time range override in this case. See here: Queries | Grafana Labs. I think if you put “30d” under “Relative time” for that panel that should do what you want.

That’s exactly what I wanted, thanks so much.

I’m going to try to create the query without the subquery for the entity where I have stable readings every 1 second!

dear All

how it’s possible to convert it from influxQL to flux?
or @jcastro maybe you have the query without subquery to share and convert to flux?
thanks in adavnce for the help

SELECT sum("mean")/3600 FROM (
  SELECT mean("value") FROM "W" WHERE entity_id='shelly_usage_watts' AND $timeFilter GROUP BY time(1s) fill(previous)
) WHERE $timeFilter GROUP BY time(1h) fill(null)

Welcome @liviomerola to the Grafana forum.

Converting from InfluxQL to Flux is best done using a “wipe it clean and start over” approach. I do not find Flux that intuitive, but others do.

Can I ask why you feel the need to convert to Flux? The thread you have commented on has a query that gathers daily consumption using InfluxQL just fine. Were you not able to get it to work?

@grant2 thanks to you for accepting me…

I use a v2 of influx db and my version of grafana doesn’t allow me to use the old InfluxQL but only flux.
Maybe I’m wrong but i didn’t find any option to use InfluxQL instead of flux.

OK, so you are set up now running Flux. Rather than composing the Flux query in Grafana, I find it much easier to use Influx Data Explorer, which is usually at http://xxxxx:8086 and then you can build it the query using the GUI, get it to display as you wish, and then copy/paste the text of the query into Grafana.