Calculation of energy-counter data

#1

Hi, I’m very new to influxdb.
I already managed to log the following energy-counters.

The data is something like this:
2019-04-15 12:00:00 c1 100
2019-04-15 12:00:00 c2 1000
2019-04-15 12:00:02 c1 101
2019-04-15 12:00:03 c2 1001
2019-04-15 12:00:03 c1 102
2019-04-15 12:00:04 c2 1002

2019-04-15 12:59:59 c1 110
2019-04-15 12:59:59 c2 1100

Now I want to calculate the total amount of consumed energy for per hour which is the sum of the difference of c1 and the difference of c2

(110-100) + (1100-1000) = 110 kWh

Is it possible with a influxdb query, or do where do I need Kapacitor?

Can you give me hints where to start?

#2

Hello @Tonny,
What does the line protocol look like? Is your data in different measurements? or are c1 and c2 tags? If you’re looking to perform math across measurements I recommend looking at this blog and this blog.

#3

I come from a SQL world and I’m still stuggeling with the naming concepts.

I’ve only one measurement, and c1 and c2 are tags. In reality there are about 50 counters (C1 … C50).

This is what I have so far.
SELECT * FROM (SELECT difference("value") FROM "kWh" WHERE time > Now() -5m group by "tag") group by "tag"

name: kWh
tags: tag=C1_Energy
time                difference
----                ----------
1555479661000000000 0.25
1555479721000000000 0.1875
1555479781000000000 0.25
1555479841000000000 0.1875

name: kWh
tags: tag=C2_Energy
time                difference
----                ----------
1555479661000000000 0.140625
1555479721000000000 0.171875
1555479781000000000 0.09375
1555479841000000000 0.109375

name: kWh
tags: tag=C3_Energy
time                difference
----                ----------
1555479661000000000 0.03515625
1555479721000000000 0.03125
1555479781000000000 0.03515625
1555479841000000000 0.03515625

I want to know the amount used by every unit (per hour, per day, per week, per month).
The formula to calculate the used energy for Unit1 = C1 + C2
The formula to calculate the used energy for Unit2 = C3

Eventually I want to have a table that looks something like this.

Time                Unit1(kWh)   Unit2(kWh)
2019-04-15 12:00      0.99             10
2019-04-15 13:00      0.12              5

Another option is that everything is stored in the measurement like this
Time1 C1=123 C2=456 C3=789 … C50 = 321
This is easier for calculation, but less flexible