i want to find out which power consumption i have over the last day (not last 24h). I can´t find any information on the inet around this. I know i can do this all manually etc but an automaton would be nice.
So here is the current setup.
I´m getting the information from my smart meter which is send via mqtt to the broker. In node red the flow is as follow:
"mqtt in" -> "json" -> "change" -> "influxdb out"
i then take the data from the influxdb and display it via grafana. Works really nice.
What i need now is the value from "yesterday 23:59:59" -(minus) "yesterday 00:00:00"
Example (as the smart meter is counting upwards the value at the end of the day is greater than of beginning of the day)
"yesterday 23:59:59" -> 15000kwh
minus
"yesterday 00:00:00" -> 14000kwh
result = 1000kwh
Ok, I did mine by storing the difference each time rather than the actual reading, which makes querying a bit easier as to get the usage over the last 24 hours you can use the cumulative_sum function. However I am sure that the difference over that last 24 hours can be queried, but it is a little more complex. Are you using the Flux query language or influxql?
Assuming you are using influxql then you can get the last reading for each day using MAX and GROUP BY(1d), so for example SELECT max("value") FROM "the_measurement" WHERE time>now()-2d and time <= now() group by time(1d)
should give you three values being the end of day readings for two days ago, yesterday and the latest reading.
Then if you take a difference on those it will give you the usage for each day SELECT difference(max("value")) FROM "the_measurement" WHERE time>now()-2d and time <= now() group by time(1d)
should give three values where the first is zero (as it has nothing to take a difference against, the second should be the usage for yesterday and the third should be the usage so far today.
Why do you want to write it to the database when all you have to do is to query it?
To get in influx today so far and not the last 24h use now()/d SELECT difference(max("value")) FROM "the_measurement" WHERE time>now()/d and time <= now() group by time(1d)
When you group by 1 day it does the grouping on day boundaries, so the query I posted does what I said. There is no need for the /d. Note that the OP is not actually after the usage so far today, he is looking for the usage yesterday.
[Edit] in fact I think the query you have suggested will not work as the inner query (the max query) will only return one row and it needs two for the difference to work.
First of all thank you for the fast help. I have to admit that my knowledge on databases is 0. I did everything by reading how to´s and adjusting them to my "needs" more or less successfull
@Colin
these are the outputs of what you suggested
SELECT max("value") FROM "tele/tasmota_D12486/total_in" WHERE time>now()-2d and time <= now() group by time(1d)
name: tele/tasmota_D12486/total_in
time max
The value 15243.723 corresponds to 09/03/2021 00:58
The value 15251.250 corresponds to 10/03/2021 00:58
The value 15255.588 corresponds to 10/03/2021 18:52 -> time i did run the command
SELECT difference(max("value")) FROM "tele/tasmota_D12486/total_in" WHERE time>now()-2d and time <= now() group by time(1d)
name: tele/tasmota_D12486/total_in
time difference
1615161600000000000 8.555000000000291
1615248000000000000 7.527000000000044 -> corresponds to difference between 09.03 to 10.03 (00:58
1615334400000000000 4.337999999999738
Maybe a dumb question but why does he start the reading at 00:58 if the first value "from the day" is on 00:02 with 15243.523
SELECT difference(max("value")) FROM "tele/tasmota_D12486/total_in" WHERE time>now()/d and time <= now() group by time(1d)
ERR: invalid operation: time and *influxql.BinaryExpr are not compatible
It is because influx assumes UTC, I assume you are 1 hour off that. You will need to specify the timezone in the command, I can't remember the syntax at the moment and not at my computer. Have a Google and see if you can work it out. If not will have a look later.
