Hello, I have a device that reports every 10 seconds the power consumption
2019-08-18T13:23:34.403Z 536
2019-08-18T13:23:45.19Z 528
2019-08-18T13:23:54.09Z 551
2019-08-18T13:24:04.016Z 565
2019-08-18T13:24:13.966Z 552
2019-08-18T13:24:23.781Z 561
2019-08-18T13:24:33.713Z 559
2019-08-18T13:24:44.253Z 546
2019-08-18T13:24:53.578Z 546
2019-08-18T13:25:03.406Z 555
2019-08-18T13:25:13.341Z 543
2019-08-18T13:25:23.308Z 549
How is the correct way to extract the dayly Kwh? I was doing
SELECT sum(“kwh”) as “kwhd” FROM ( SELECT ( sum(“watt”) / 1000 ) * ( 10 / 3600 ) as “kwh” FROM ( SELECT mean(“power”) as “watt” FROM “power” WHERE (“deviceId” =~ /^main$/) AND time >= now() - 15d GROUP BY time(10s) ) GROUP BY time(1h) ) GROUP BY time(1d)
019-08-03T00:00:00Z 4.736919444444444
2019-08-04T00:00:00Z 12.499451388888888
2019-08-05T00:00:00Z 17.54595138888889
2019-08-06T00:00:00Z 17.212866666666663
2019-08-07T00:00:00Z 13.213569444444442
2019-08-08T00:00:00Z 9.445593055555554
2019-08-09T00:00:00Z 17.090888888888887
2019-08-10T00:00:00Z 18.000631944444446
2019-08-11T00:00:00Z 13.173834722222221
2019-08-12T00:00:00Z 11.927722222222222
2019-08-13T00:00:00Z 15.160688888888888
2019-08-14T00:00:00Z 22.07599861111111
2019-08-15T00:00:00Z 24.170081944444437
2019-08-16T00:00:00Z 18.734087037037035
2019-08-17T00:00:00Z 20.922583912037034
2019-08-18T00:00:00Z 27.638266666666674
But one thing that is strange is the “mean(“power”)” this is doing the average in the interval of 10s… I would like to downsample it to a interval of 1m, but keep the correct values, if I change the interval to 1 minute I would get
SELECT sum(“kwh”) as “kwhd” FROM ( SELECT ( sum(“watt”) / 1000 ) * ( 60 / 3600 ) as “kwh” FROM ( SELECT mean(“power”) as “watt” FROM “power” WHERE (“deviceId” =~ /^main$/) AND time >= now() - 15d GROUP BY time(60s) ) GROUP BY time(1h) ) GROUP BY time(1d)
2019-08-03T00:00:00Z 4.736806746031746
2019-08-04T00:00:00Z 12.598218968253969
2019-08-05T00:00:00Z 17.741833293650796
2019-08-06T00:00:00Z 17.344608293650797
2019-08-07T00:00:00Z 13.371461230158733
2019-08-08T00:00:00Z 9.553692777777776
2019-08-09T00:00:00Z 17.254985317460317
2019-08-10T00:00:00Z 18.152161706349204
2019-08-11T00:00:00Z 13.300476587301585
2019-08-12T00:00:00Z 12.031188095238095
2019-08-13T00:00:00Z 15.313914087301587
2019-08-14T00:00:00Z 22.43567357142857
2019-08-15T00:00:00Z 24.370615079365084
2019-08-16T00:00:00Z 19.27941738095238
2019-08-17T00:00:00Z 21.27893458180708
2019-08-18T00:00:00Z 27.931079166666667
Is it possible to do this without the average? The sum does not work also
SELECT sum(“kwh”) as “kwhd” FROM ( SELECT ( sum(“watt”) / 1000 ) * ( 10 / 3600 ) as “kwh” FROM ( SELECT sum(“power”) as “watt” FROM “power” WHERE (“deviceId” =~ /^main$/) AND time >= now() - 15d GROUP BY time(10s) ) GROUP BY time(1h) ) GROUP BY time(1d)
2019-08-03T00:00:00Z 4.748808333333334
2019-08-04T00:00:00Z 12.659838888888887
2019-08-05T00:00:00Z 17.784555555555556
2019-08-06T00:00:00Z 17.46396388888889
2019-08-07T00:00:00Z 13.40831388888889
2019-08-08T00:00:00Z 9.567997222222223
2019-08-09T00:00:00Z 17.30015833333333
2019-08-10T00:00:00Z 18.23287777777778
2019-08-11T00:00:00Z 13.368561111111111
2019-08-12T00:00:00Z 12.085250000000002
2019-08-13T00:00:00Z 15.387980555555558
2019-08-14T00:00:00Z 22.411555555555555
2019-08-15T00:00:00Z 24.46866388888889
2019-08-16T00:00:00Z 19.02095
2019-08-17T00:00:00Z 21.250827777777776
2019-08-18T00:00:00Z 28.07861111111111
and with 60s (even worse)
2019-08-03T00:00:00Z 28.46766666666667
2019-08-04T00:00:00Z 75.95903333333334
2019-08-05T00:00:00Z 106.70733333333331
2019-08-06T00:00:00Z 104.78378333333333
2019-08-07T00:00:00Z 80.44988333333332
2019-08-08T00:00:00Z 57.40798333333333
2019-08-09T00:00:00Z 103.80095
2019-08-10T00:00:00Z 109.39726666666665
2019-08-11T00:00:00Z 80.21136666666666
2019-08-12T00:00:00Z 72.5115
2019-08-13T00:00:00Z 92.32788333333333
2019-08-14T00:00:00Z 134.46933333333334
2019-08-15T00:00:00Z 146.81198333333333
2019-08-16T00:00:00Z 114.12570000000002
2019-08-17T00:00:00Z 127.50496666666666
2019-08-18T00:00:00Z 168.51646666666667
What is the correct form to calculate the kwh daily in this situation?
Thank you
