Also another graph to show total consumption for a particular time frame selected
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
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?
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
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 measurementshelly_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.
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
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!
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)
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.
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)
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?
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.
