Dashboard SQLite Query Group by Day

Hi,

i have a simple SQLite database which contains a timestamp, a value with a current consumption and a value with summarize the consumption. The values are updated every 10 seconds.

Now i tried to make a dashboard which summarize the consumption for each day (for the last 7 days). I thought this is a easy think. But im failed…

This are my Tables:

i tried this which calculate the difference from the beginning to the end of database:

But when i group it by “zeit” than there is no group and the value is zero

Of course i tried much much more and yes, i also used google…
Now the question, where is my fault? What i have to change?

Welcome

So your issue is really not grafana but sqlite query language. You would get more traction if you posted on a sqllite forum. But that said what you have is not being grouped by anything neither by Zeit nor by date

All you are doing is taking the max and min and getting the difference.

I would encourage you to read this doco

Hi,

sorry i forgot the last screenshot. When i add the “group by query” than nothing happens. But nothing is not correct. The value of “Tagesverbrauch” switch to zero…

The target should be, that the query take the min and max value (of each day) and calculate the consumption for each day. But im not able to Group the values day by day and im not sure, what is the problem. Is the problem the format of the time, is there a problem with the query, or is there a other problem.

1 Like

Did you read the doco I posted?

Here is a good forum yo ask this question at.

https://sqlite.org/forum/forum

Just as a hint. You indeed have an SQL problem (nothing about Grafana or the plugin).

When you do “group by zeit”, then in SQL all the values with the same Zeit value are grouped. This makes no sense in your case as you only have one value per Zeit. With only one value your grouping does nothing and the min and max are the same value (which makes the difference 0 of course).

You can solve your issue by writing proper SQL.
You can also try reading up here: grafana-sqlite-datasource/examples.md at master · fr-ser/grafana-sqlite-datasource · GitHub

The “__unixEpochGroupSeconds” macro of the plugin should do what you need.