Wattmeter data from sqlite

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:

|ID|timestamp            |kWh   |
|1 | 2019-07-31 14:24:25 | 270.8|
|2 | 2019-07-31 14:31:02 | 272.1|
|3 | 2019-08-01 06:56:45 | 382.5|
|3 | 2019-08-01 16:29:01 | 382.5|
|3 | 2019-08-01 20:32:53 | 582.5|
|3 | 2019-08-02 16:18:14 | 612.1|
  • The data interval is not fixed.
  • There can be a day with no records at all (data transmission failure for example).
  • There can be many records with the identical (equal) power consumption (no energy consumption).

My qestion is how to write SQL select to get summarized energy consumption for required intervals (days or weeks or months ...)

It looks like simple question but I am pulling out my hair for two days to find a solution.


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.

this is not this case. power meter reading is going up. You can not to summarize it.

Which "this" are your referring to?

Readings are total readings (readings from commercial power meters). And yes, it is not trivial really :smiley: It is kind of challenge :smiley: That is why I am asking here.
I am really interested if other users of nod-red are bearing the same problem.

sory, my mystake, my reply was to: ukmoose

(Wattmeter data from sqlite)


Well I have provided the algorithim to use, you just have to write some code to implement it :slight_smile:

I know the algorithm. Thanks anyway.
Of course I can write something complex in Python for example.
But I am looking for SQL select.

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 ...

What is the query?

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
preenumerated pre
on en.cnt = pre.cnt

		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 
období od	období do	počáteční kWh	koncový kWh	kWh za období	počet dnů	kWh za den
29.07.2019	30.07.2019	0.0	150.0	150.0	1.0	150.0
30.07.2019	31.07.2019	150.0	346.7	196.7	1.0	196.7
31.07.2019	01.08.2019	346.7	551.1	204.4	1.0	204.4
01.08.2019	02.08.2019	551.1	755.2	204.1	1.0	204.1
02.08.2019	03.08.2019	755.2	939.9	184.7	1.0	184.7
03.08.2019	04.08.2019	939.9	1117.0	177.1	1.0	177.1
04.08.2019	05.08.2019	1117.0	1309.4	192.4	1.0	192.4
05.08.2019	06.08.2019	1309.4	1494.0	184.6	1.0	184.6
06.08.2019	07.08.2019	1494.0	1683.4	189.4	1.0	189.4
29.07.2019	07.08.2019		CELKEM:	1683.4 kWh	za 9 dnů	ø 187.0 kWh/den

Petr, instead of continually deleting your posts & starting a new one... why not simply edit the post?
(the pencil icon)

1 Like

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.

Sorry, you are right. I am not keen using it here. I will improve.

1 Like

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.

GROUP BY time(4w) is the only solution I found for months.