Hey! Ok I cant solve this problem. I have a sensor with increasing kWh.
And and I did use group by 1 day but how do I make it show me the total increased per day?
I want one bar per day showing me the total usage for that day. Is that possible?
@mrglad8 what version are you on? And could you provide sample data? for example as csv
Datetime,KWh
2022-04-26 11:15:30,14.7
2022-04-26 11:30:30,11.7
2022-04-26 11:45:30,23.7
2022-04-27 11:15:30,21.7
2022-04-28 11:15:30,22.7
2022-04-29 11:15:30,0.7
2022-04-30 11:15:30,4.7
2022-05-01 11:15:30,7.7
2022-05-02 11:15:30,41.7
2022-05-03 11:15:30,55.7
2022-05-04 11:15:30,4.7
I use Current version: 7.5.2.
Hm a csv… let me try:
SELECT distinct(“value”) FROM “kWh” WHERE (“entity_id” = ‘cumulative_active_import’) AND $timeFilter GROUP BY time(1d) fill(null)
Oh and also, 1 day seem to count the time 02:00 to 02:00 … why? How to make it count only the hours of the current day?
Here I seem to kinda get it working:
But first of all it counts from 02:00 , thats not when a day starts?
And I only want the increased number of each day i/e the daily useage… is that so hard to get working? Hm … what am I missing?
1 Like
Could you export the raw data from influx as csv, change any confidential data and post it here? We dont have access to your data so providing sample csv data helps us help you
The data is stored in UTC, but you can change the timezone by changing the TIMEZONE field.
(Europe/Oslo is my timezone).
The timezone name is the “TZ database name” column from List of tz database time zones - Wikipedia
1 Like
How do I do that? I dont think there is much to show though since I really haven’t got anything together! Isn’t there just a button to press to show the increased data per day? This must be quite a standard thing to have in everyone’s energy overview?
I mean I already have everything I want in Home Assistant’s own Energy Monitoring/Dashboard
. But that data is stored locally on HA. And I would like to have the same overview in Grafana. So not only daily usage but also hourly etc… I use the same sensor in Grafana as I do in HA’s own energy dashboard. But the question is… how do I get this same overview in Grafana? Is that even possible?
There is a bar option in Grafana as well. The graph is default lines, but can be changed to bars in the display options of the panel.
To get the time at the bottom you have to set X axis to be Time in the Axis options of the panel.
Edit:
There is also a bar chart in beta.
In the new graph panel the option is called Graph styles. (On the new graph panel there is no need to change the x axis).
Thanks!
I think I managed to get the bars working. But I only manage to get them to show the increasing numbers. All I want is each day’s bar to show that days consumption. How do I do that?
export it from within influxdb
Please see this thread to see importance of providing sample data
Hm not sure I follow you want my influx raw data? But so there is no button/setting here to simply “show daily summarized data” ? I guess I have to do some kind of template sensor for that then?
You could add a select field with difference.
Made an example dashboard
Daily consumption panel:
Total consumption panel:
Dashboard JSON Model
{
"annotations": {
"list": [
{
"builtIn": 1,
"datasource": "-- Grafana --",
"enable": true,
"hide": true,
"iconColor": "rgba(0, 211, 255, 1)",
"name": "Annotations & Alerts",
"target": {
"limit": 100,
"matchAny": false,
"tags": [],
"type": "dashboard"
},
"type": "dashboard"
}
]
},
"editable": true,
"fiscalYearStartMonth": 0,
"graphTooltip": 0,
"id": 35,
"links": [],
"liveNow": false,
"panels": [
{
"datasource": {
"type": "influxdb",
"uid": "xEOp3Uwnz"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "palette-classic"
},
"custom": {
"axisLabel": "",
"axisPlacement": "auto",
"barAlignment": 0,
"drawStyle": "bars",
"fillOpacity": 30,
"gradientMode": "none",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"lineInterpolation": "linear",
"lineWidth": 1,
"pointSize": 5,
"scaleDistribution": {
"type": "linear"
},
"showPoints": "auto",
"spanNulls": false,
"stacking": {
"group": "A",
"mode": "none"
},
"thresholdsStyle": {
"mode": "off"
}
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 13,
"w": 24,
"x": 0,
"y": 0
},
"id": 2,
"options": {
"legend": {
"calcs": [],
"displayMode": "list",
"placement": "bottom"
},
"tooltip": {
"mode": "single",
"sort": "none"
}
},
"targets": [
{
"alias": "Daily consumption",
"datasource": {
"type": "influxdb",
"uid": "xEOp3Uwnz"
},
"groupBy": [
{
"params": [
"1d"
],
"type": "time"
},
{
"params": [
"null"
],
"type": "fill"
}
],
"hide": false,
"measurement": "test",
"orderByTime": "ASC",
"policy": "default",
"refId": "A",
"resultFormat": "time_series",
"select": [
[
{
"params": [
"value"
],
"type": "field"
},
{
"params": [],
"type": "distinct"
},
{
"params": [],
"type": "difference"
}
]
],
"tags": [
{
"key": "test",
"operator": "=",
"value": "test"
}
],
"tz": "Europe/Oslo"
}
],
"title": "Daily consumption",
"type": "timeseries"
},
{
"datasource": {
"type": "influxdb",
"uid": "xEOp3Uwnz"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "palette-classic"
},
"custom": {
"axisLabel": "",
"axisPlacement": "auto",
"barAlignment": 0,
"drawStyle": "line",
"fillOpacity": 0,
"gradientMode": "none",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"lineInterpolation": "linear",
"lineWidth": 1,
"pointSize": 5,
"scaleDistribution": {
"type": "linear"
},
"showPoints": "auto",
"spanNulls": false,
"stacking": {
"group": "A",
"mode": "none"
},
"thresholdsStyle": {
"mode": "off"
}
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 13,
"w": 24,
"x": 0,
"y": 13
},
"id": 3,
"options": {
"legend": {
"calcs": [],
"displayMode": "list",
"placement": "bottom"
},
"tooltip": {
"mode": "single",
"sort": "none"
}
},
"targets": [
{
"alias": "Total consumption",
"datasource": {
"type": "influxdb",
"uid": "xEOp3Uwnz"
},
"groupBy": [
{
"params": [
"1d"
],
"type": "time"
},
{
"params": [
"null"
],
"type": "fill"
}
],
"hide": false,
"measurement": "test",
"orderByTime": "ASC",
"policy": "default",
"refId": "A",
"resultFormat": "time_series",
"select": [
[
{
"params": [
"value"
],
"type": "field"
},
{
"params": [],
"type": "distinct"
}
]
],
"tags": [
{
"key": "test",
"operator": "=",
"value": "test"
}
],
"tz": "Europe/Oslo"
}
],
"title": "Total consumption",
"type": "timeseries"
}
],
"refresh": false,
"schemaVersion": 35,
"style": "dark",
"tags": [],
"templating": {
"list": []
},
"time": {
"from": "2022-04-10T22:00:00.000Z",
"to": "2022-04-27T21:59:59.000Z"
},
"timepicker": {},
"timezone": "",
"title": "Test graph daily consumption",
"uid": "YJ7q6Uw7z",
"version": 2,
"weekStart": ""
}
2 Likes
There! Awesome, thank you! Exactly what I was looking for. That “Difference” was the magical setting 
Oh I checked it in detail, and it seems to be one day off in its values pure visual. The daily consumption of 26th is shown in the 27th bar. Is there some way to get that right? Hm
Actually when I compare both Mean and Distinct to the true values the Distinct gives more accurate values! With the 1day off difference.
Not sure what the Mean version of today show in value. Because its morning and I havent used 13.4 kWh! And the last bar in Distinct one is 21.4 and that correct but for yesterday’s useage.
