I hope my question is not "off topic" really. Many users here are using node-red to store energy (gas, water, electricity meters...) consumption data to the sql table.
The simple table to store kWh consumption can look like this:
Are those the instantaneous power readings or the accumulated total readings? I think the totals, in which case you need to take the difference between the readings at the start and end time. So if for example you needed the total for 2019-08-01 you would first have to estimate the value at the start of the day, possibly be reading the last one before the start of that day and the first one on that day and interpolating between them, then do the same for the end of the day and then take the difference. That is going to need some non-trivial code.
Readings are total readings (readings from commercial power meters). And yes, it is not trivial really It is kind of challenge That is why I am asking here.
I am really interested if other users of nod-red are bearing the same problem.
You don't need to do it in python you can do it in javascript in node-red. I don't know how you would do it SQL. It may be possible but you would need an SQL expert.
I am just trying to find some node-red users bearing the same problem.
I think a lot of people here are reading energy meters and are visualising data and so on...
I did write SQL query already. Kind of complex. But it still has few issues ...
WITH consumption as (
select date(TIMESTAMP) as d, min(TOTAL_KWH) mi, max(TOTAL_KWH) ma
from power
group by date(timestamp)
)
, enumerated as (
select * ,
(select count(*) from consumption b where a.d >= b.d) as cnt
from consumption a
)
, preenumerated as (
select * ,
(select count(*)-1 from consumption b where a.d >= b.d) as cnt
from consumption a
)
select strftime('%d.%m.%Y', en.d) as "obdobĂ od", strftime('%d.%m.%Y',pre.d) as "obdobĂ do",
printf("%.1f", en.ma) as "počátečnà kWh",
printf("%.1f", pre.ma) as "koncovĂ˝ kWh",
printf("%.1f", pre.ma-en.ma) as "kWh za obdobĂ",
(JulianDay(pre.d) - JulianDay(en.d)) as "poÄŤet dnĹŻ",
printf("%.1f", (pre.ma-en.ma)/(JulianDay(pre.d) - JulianDay(en.d))) as "kWh za den"
from enumerated en
join
preenumerated pre
on en.cnt = pre.cnt
UNION ALL
select strftime('%d.%m.%Y', min(date(TIMESTAMP))) as "obdobĂ od",
strftime('%d.%m.%Y',max(date(TIMESTAMP))) as "obdobĂ do",
"" as "počátečnà kWh",
"CELKEM:" as "koncovĂ˝ kWh",
printf("%.1f kWh", max(TOTAL_KWH)) as "kWh za obdobĂ",
printf("za %.0f dnĹŻ", (JulianDay(max(date(TIMESTAMP))) - JulianDay(min(date(TIMESTAMP))))) as "poÄŤet dnĹŻ",
printf("ø %.1f kWh/den", (max(TOTAL_KWH)-min(TOTAL_KWH))/(JulianDay(max(date(TIMESTAMP))) - JulianDay(min(date(TIMESTAMP))))) as "kWh za den"
from power
But this SELECT does not cover the situation, when data is interupted for few (say 3) days.
I would like to have day rows with average consumption for this scenario.
Instead I have ONE row with 3 day consumption and the average day consumption.
Well apparently your SQL skills are way above mine and I suspect most (all?) here.
I suspect you will have to get help elsewhere or do what I would do, which is use a sequence of four basic queries to get the required data and then do the simple maths to get the answer.
In other discussion group I was pointed to the time series databases to solve this problem.
I did some experiments with InfluxDB, which was nice at beginning (see my short blogpost about it).
After first "hurray" I did realized InfluxDB doesn't have date "machinery" (months, weeks ...) incorporated. So to get data summarized by months for example is nearly not possible (maybe lack of my knowledge only, but I did not find any solution).
So finally I went back to the Sqlite and refined (simplified) SQL SELECT
To solve the problem when days with no records at all (data transmission failure for example) I will write the external script to watch the database and inserting averages for "empty" days.
It is kind of challenge 
