# Value calculation "last day"
**URL:** https://discourse.nodered.org/t/value-calculation-last-day/42419
**Category:** General
**Created:** [10 March 2021 15:20 UTC](https://discourse.nodered.org/t/value-calculation-last-day/42419 "2021-03-10T15:20:07Z")
**Posts on this page:** 13
**Page:** 1
### Author:  [@joselitoo](https://discourse.nodered.org/u/joselitoo)
#### Post date: [10 March 2021 15:20 UTC](https://discourse.nodered.org/t/value-calculation-last-day/42419/1 "2021-03-10T15:20:07Z")
Hi,
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
Finally write this result to the influxdb
Any ideas how i can do this in node red?
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [10 March 2021 15:37 UTC](https://discourse.nodered.org/t/value-calculation-last-day/42419/2 "2021-03-10T15:37:44Z")
Welcome to the forum @joselitoo.
Exactly what are you writing to influx at the moment? Are you writing the actual meter reading each time?
---
### Author:  [@joselitoo](https://discourse.nodered.org/u/joselitoo)
#### Post date: [10 March 2021 15:42 UTC](https://discourse.nodered.org/t/value-calculation-last-day/42419/3 "2021-03-10T15:42:39Z")
Yes exactly.
In influxdb it looks like this
1615383486520400254 15254.278
1615383786546538725 15254.296
1615384086540829751 15254.316
1615384386524961058 15254.337
1615384686527286463 15254.358
1615384986594725625 15254.379
1615385286553416876 15254.4
1615385586575007158 15254.419
1615385886576023897 15254.433
1615386186617345779 15254.451
1615386486613068987 15254.478
1615386786573388627 15254.501
1615387086636809383 15254.524
1615387386623909877 15254.55
1615387686626022876 15254.579
1615387986662752092 15254.607
1615388286645805287 15254.63
1615388586656002532 15254.65
1615388886641831354 15254.673
1615389186659684584 15254.693
1615389486686025054 15254.717
1615389786699595545 15254.745
1615390086705890842 15254.773
1615390386700310743 15254.798
1615390686744015554 15254.818
In grafana it looks like this
2021-03-09 00:02:00 **15243.532**
2021-03-09 23:58:00 **15250.945**
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [10 March 2021 15:47 UTC](https://discourse.nodered.org/t/value-calculation-last-day/42419/4 "2021-03-10T15:47:59Z")
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?
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [10 March 2021 16:23 UTC](https://discourse.nodered.org/t/value-calculation-last-day/42419/5 "2021-03-10T16:23:32Z")
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?
---
### Author:  [@edje11](https://discourse.nodered.org/u/edje11)
#### Post date: [10 March 2021 16:47 UTC](https://discourse.nodered.org/t/value-calculation-last-day/42419/6 "2021-03-10T16:47:32Z")
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)`
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [10 March 2021 17:12 UTC](https://discourse.nodered.org/t/value-calculation-last-day/42419/7 "2021-03-10T17:12:12Z")
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.
---
### Author:  [@joselitoo](https://discourse.nodered.org/u/joselitoo)
#### Post date: [10 March 2021 18:07 UTC](https://discourse.nodered.org/t/value-calculation-last-day/42419/8 "2021-03-10T18:07:20Z")
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
* * *
1615161600000000000 15243.723
1615248000000000000 15251.25
1615334400000000000 15255.588
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
@edje11
> **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
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [10 March 2021 18:35 UTC](https://discourse.nodered.org/t/value-calculation-last-day/42419/9 "2021-03-10T18:35:47Z")
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.
---
### Author:  [@joselitoo](https://discourse.nodered.org/u/joselitoo)
#### Post date: [10 March 2021 19:01 UTC](https://discourse.nodered.org/t/value-calculation-last-day/42419/10 "2021-03-10T19:01:55Z")
@Colin
You are absolutely right. i just added the timezone to the command "tz('Europe/Berlin')"
SELECT difference(max("value")) FROM "tele/tasmota\_D12486/total\_in" WHERE time\>now()-2d and time \<= now() group by time(1d) tz('Europe/Berlin')
maybe another dumb question how would i do it for weeks months and years?
for weeks i would to "....WHERE time\>now()-7d and time \<= now()...."
EDIT:
found it "w" for week is the highest. What is the best "way" to write it -7d or -1w? the outcome is the same but whats more common used.
---
### Author:  [@edje11](https://discourse.nodered.org/u/edje11)
#### Post date: [10 March 2021 20:22 UTC](https://discourse.nodered.org/t/value-calculation-last-day/42419/11 "2021-03-10T20:22:57Z")
Sorry, my mistake, it does indeed not work.
Messed up influx query and Grafana Time range control.
> **[Time range controls](https://grafana.com/docs/grafana/latest/dashboards/time-range-controls/)**
>
> Time range controls Grafana provides several ways to manage the time ranges of the data being visualized, both at the dashboard level and the panel level. …
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [10 March 2021 20:57 UTC](https://discourse.nodered.org/t/value-calculation-last-day/42419/12 "2021-03-10T20:57:09Z")
> [@edje11](#):
>
> Sorry, my mistake, it does indeed not work.
What doesn't work? You won't be able to plug it directly into Grafana. What exactly do you want to show in grafana?
---
### Author:  [@system](https://discourse.nodered.org/u/system)
#### Post date: [9 May 2021 20:57 UTC](https://discourse.nodered.org/t/value-calculation-last-day/42419/13 "2021-05-09T20:57:56Z")
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.