1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
| WITH time_series AS (
SELECT
generate_series(
date_trunc('hour', TO_TIMESTAMP(EXTRACT(EPOCH FROM $__timeFrom()::timestamp))),
date_trunc('hour', TO_TIMESTAMP(EXTRACT(EPOCH FROM $__timeTo()::timestamp))),
interval '1 hour'
) AS time
),
solar_hourly_max AS (
SELECT
date_trunc('hour', TO_TIMESTAMP(s.last_updated_ts)) AS time,
MAX(s.state::float) AS max_value
FROM
states s
JOIN
states_meta m ON s.metadata_id = m.metadata_id
WHERE
m.entity_id = 'sensor.solaredge_modbus_ac_energy_kwh'
AND s.state ~ '^[0-9.]+$'
AND s.last_updated_ts BETWEEN EXTRACT(EPOCH FROM $__timeFrom()::timestamp)
AND EXTRACT(EPOCH FROM $__timeTo()::timestamp)
GROUP BY
date_trunc('hour', TO_TIMESTAMP(s.last_updated_ts))
),
solar_joined AS (
SELECT
ts.time,
hm.max_value
FROM
time_series ts
LEFT JOIN solar_hourly_max hm ON ts.time = hm.time
),
solar_filled AS (
SELECT
time,
-- Fill nulls using the last known value
MAX(max_value) OVER (ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS filled_value
FROM
solar_joined
),
sessy_charged_hourly_max AS (
SELECT
date_trunc('hour', TO_TIMESTAMP(s.last_updated_ts)) AS time,
MAX(s.state::float) AS max_value
FROM
states s
JOIN
states_meta m ON s.metadata_id = m.metadata_id
WHERE
m.entity_id = 'sensor.sessy_all_charged_energy'
AND s.state ~ '^[0-9.]+$'
AND s.last_updated_ts BETWEEN EXTRACT(EPOCH FROM $__timeFrom()::timestamp)
AND EXTRACT(EPOCH FROM $__timeTo()::timestamp)
GROUP BY
date_trunc('hour', TO_TIMESTAMP(s.last_updated_ts))
),
sessy_charged_joined AS (
SELECT
ts.time,
hm.max_value
FROM
time_series ts
LEFT JOIN sessy_charged_hourly_max hm ON ts.time = hm.time
),
sessy_charged_filled AS (
SELECT
time,
-- Fill nulls using the last known value
MAX(max_value) OVER (ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS filled_value
FROM
sessy_charged_joined
),
sessy_discharged_hourly_max AS (
SELECT
date_trunc('hour', TO_TIMESTAMP(s.last_updated_ts)) AS time,
MAX(s.state::float) AS max_value
FROM
states s
JOIN
states_meta m ON s.metadata_id = m.metadata_id
WHERE
m.entity_id = 'sensor.sessy_all_discharged_energy'
AND s.state ~ '^[0-9.]+$'
AND s.last_updated_ts BETWEEN EXTRACT(EPOCH FROM $__timeFrom()::timestamp)
AND EXTRACT(EPOCH FROM $__timeTo()::timestamp)
GROUP BY
date_trunc('hour', TO_TIMESTAMP(s.last_updated_ts))
),
sessy_discharged_joined AS (
SELECT
ts.time,
hm.max_value
FROM
time_series ts
LEFT JOIN sessy_discharged_hourly_max hm ON ts.time = hm.time
),
sessy_discharged_filled AS (
SELECT
time,
-- Fill nulls using the last known value
MAX(max_value) OVER (ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS filled_value
FROM
sessy_discharged_joined
),
solar_delta AS (
SELECT
time,
filled_value - LAG(filled_value) OVER (ORDER BY time) AS kwh
FROM
solar_filled
ORDER BY
time
),
sessy_charged_delta AS (
SELECT
time,
filled_value - LAG(filled_value) OVER (ORDER BY time) AS kwh
FROM
sessy_charged_filled
ORDER BY
time
),
sessy_discharged_delta AS (
SELECT
time,
filled_value - LAG(filled_value) OVER (ORDER BY time) AS kwh
FROM
sessy_discharged_filled
ORDER BY
time
),
-- Get hourly max from delivered electricity sensor
delivered_1_raw AS (
SELECT
date_trunc('hour', TO_TIMESTAMP(s.last_updated_ts)) AS time,
MAX(s.state::float) AS max_value
FROM
states s
JOIN states_meta m ON s.metadata_id = m.metadata_id
WHERE
m.entity_id = 'sensor.dsmr_reading_electricity_delivered_1'
AND s.state ~ '^[0-9.]+$'
AND s.last_updated_ts BETWEEN EXTRACT(EPOCH FROM $__timeFrom()::timestamp)
AND EXTRACT(EPOCH FROM $__timeTo()::timestamp)
GROUP BY 1
),
-- Get hourly max from delivered electricity sensor
delivered_2_raw AS (
SELECT
date_trunc('hour', TO_TIMESTAMP(s.last_updated_ts)) AS time,
MAX(s.state::float) AS max_value
FROM
states s
JOIN states_meta m ON s.metadata_id = m.metadata_id
WHERE
m.entity_id = 'sensor.dsmr_reading_electricity_delivered_2'
AND s.state ~ '^[0-9.]+$'
AND s.last_updated_ts BETWEEN EXTRACT(EPOCH FROM $__timeFrom()::timestamp)
AND EXTRACT(EPOCH FROM $__timeTo()::timestamp)
GROUP BY 1
),
-- Get hourly max from delivered electricity sensor
returned_1_raw AS (
SELECT
date_trunc('hour', TO_TIMESTAMP(s.last_updated_ts)) AS time,
MAX(s.state::float) AS max_value
FROM
states s
JOIN states_meta m ON s.metadata_id = m.metadata_id
WHERE
m.entity_id = 'sensor.dsmr_reading_electricity_returned_1'
AND s.state ~ '^[0-9.]+$'
AND s.last_updated_ts BETWEEN EXTRACT(EPOCH FROM $__timeFrom()::timestamp)
AND EXTRACT(EPOCH FROM $__timeTo()::timestamp)
GROUP BY 1
),
-- Get hourly max from delivered electricity sensor
returned_2_raw AS (
SELECT
date_trunc('hour', TO_TIMESTAMP(s.last_updated_ts)) AS time,
MAX(s.state::float) AS max_value
FROM
states s
JOIN states_meta m ON s.metadata_id = m.metadata_id
WHERE
m.entity_id = 'sensor.dsmr_reading_electricity_returned_2'
AND s.state ~ '^[0-9.]+$'
AND s.last_updated_ts BETWEEN EXTRACT(EPOCH FROM $__timeFrom()::timestamp)
AND EXTRACT(EPOCH FROM $__timeTo()::timestamp)
GROUP BY 1
),
-- Fill missing hours by carrying last known value
delivered_1_filled AS (
SELECT
ts.time,
MAX(her.max_value) OVER (ORDER BY ts.time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS delivered_1_raw_value
FROM
time_series ts
LEFT JOIN delivered_1_raw her ON ts.time = her.time
),
-- Calculate hourly delta in energy
delivered_1_delta AS (
SELECT
time,
delivered_1_raw_value - LAG(delivered_1_raw_value) OVER (ORDER BY time) AS kwh
FROM
delivered_1_filled
),
-- Fill missing hours by carrying last known value
delivered_2_filled AS (
SELECT
ts.time,
MAX(her.max_value) OVER (ORDER BY ts.time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS delivered_2_raw_value
FROM
time_series ts
LEFT JOIN delivered_2_raw her ON ts.time = her.time
),
-- Calculate hourly delta in energy
delivered_2_delta AS (
SELECT
time,
delivered_2_raw_value - LAG(delivered_2_raw_value) OVER (ORDER BY time) AS kwh
FROM
delivered_2_filled
),
-- Fill missing hours by carrying last known value
returned_1_filled AS (
SELECT
ts.time,
MAX(her.max_value) OVER (ORDER BY ts.time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS returned_1_raw_value
FROM
time_series ts
LEFT JOIN returned_1_raw her ON ts.time = her.time
),
-- Calculate hourly delta in energy
returned_1_delta AS (
SELECT
time,
returned_1_raw_value - LAG(returned_1_raw_value) OVER (ORDER BY time) AS kwh
FROM
returned_1_filled
),
-- Fill missing hours by carrying last known value
returned_2_filled AS (
SELECT
ts.time,
MAX(her.max_value) OVER (ORDER BY ts.time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS returned_2_raw_value
FROM
time_series ts
LEFT JOIN returned_2_raw her ON ts.time = her.time
),
-- Calculate hourly delta in energy
returned_2_delta AS (
SELECT
time,
returned_2_raw_value - LAG(returned_2_raw_value) OVER (ORDER BY time) AS kwh
FROM
returned_2_filled
),
-- Get hourly average price
price_per_hour AS (
SELECT
date_trunc('hour', TO_TIMESTAMP(s.last_updated_ts)) AS time,
AVG(s.state::float) AS price_eur_per_kwh
FROM
states s
JOIN states_meta m ON s.metadata_id = m.metadata_id
WHERE
m.entity_id = 'sensor.current_electricity_price_all_in'
AND s.state ~ '^[0-9.]+$'
AND s.last_updated_ts BETWEEN EXTRACT(EPOCH FROM $__timeFrom()::timestamp)
AND EXTRACT(EPOCH FROM $__timeTo()::timestamp)
GROUP BY 1
),
net_delivered_delta AS (
SELECT
oner.time,
(oned.kwh + twod.kwh) - (oner.kwh + twor.kwh) AS kwh
FROM
returned_1_delta oner
INNER JOIN
returned_2_delta twor ON oner.time = twor.time
INNER JOIN
delivered_1_delta oned ON oner.time = oned.time
INNER JOIN
delivered_2_delta twod ON oner.time = twod.time
ORDER BY
oner.time
),
sessy_in_delta AS (
SELECT
charged.time,
charged.kwh - discharged.kwh AS kwh
FROM
sessy_charged_delta charged
INNER JOIN
sessy_discharged_delta discharged ON charged.time = discharged.time
ORDER BY
charged.time
),
huis_delta AS (
SELECT
dsmr.time,
dsmr.kwh + solar.kwh - sessyin.kwh AS kwh
FROM
net_delivered_delta dsmr
INNER JOIN
sessy_in_delta sessyin ON dsmr.time = sessyin.time
INNER JOIN
solar_delta solar ON dsmr.time = solar.time
ORDER BY
dsmr.time
),
huis_delta_pv AS (
SELECT
huis.time,
huis.kwh - solar.kwh AS kwh
FROM
huis_delta huis
INNER JOIN
solar_delta solar ON huis.time = solar.time
ORDER BY
huis.time
),
-- Final output: kWh * €/kWh = €
costs AS (
SELECT
e.time,
e.kwh,
p.price_eur_per_kwh,
e.kwh * p.price_eur_per_kwh AS cost_eur_no_bat_no_pv,
pv.kwh * p.price_eur_per_kwh AS cost_eur_no_bat,
e.kwh * 0.34 AS cost_eur_greenchoice,
net.kwh * p.price_eur_per_kwh AS cost_dynamic,
e.kwh * 0.34 - net.kwh * p.price_eur_per_kwh AS benefit
FROM
huis_delta e
INNER JOIN
price_per_hour p ON e.time = p.time
INNER JOIN
huis_delta_pv pv ON e.time = pv.time
INNER JOIN
net_delivered_delta net ON e.time = net.time
ORDER BY
e.time
)
-- Add running total cost
SELECT
time,
SUM(cost_eur_no_bat_no_pv) OVER (ORDER BY time) AS "Kosten dynamisch zonder panelen zonder batterij",
SUM(cost_eur_no_bat) OVER (ORDER BY time) AS "Kosten dynamisch zonder batterij",
SUM(cost_eur_greenchoice) OVER (ORDER BY time) AS "Kosten geen panelen/bat en vast tarief",
SUM(cost_dynamic) OVER (ORDER BY time) AS "Kosten huidig",
SUM(benefit) OVER (ORDER BY time) AS "Voordeel tov geen panelen/bat en vast tarief"
FROM
costs
ORDER BY
time; |
/u/1806466/crop64b3d3415e06b_cropped.png?f=community)
/u/724164/crop660be643afa86_cropped.png?f=community)
:strip_icc():strip_exif()/u/661060/crop5db72c2f4eee5.jpeg?f=community)
Ik wacht nu nog steeds op versie 1.9.2 
/u/363274/crop66c2f9ac42fe8_cropped.png?f=community)
En je hoeft echt niet bang te zijn voor een vloedgolf aan mensen die ook in de beta groep willen. Veruit de meerderheid van de mensen hoeft niet per se eerste voorproever te zijn...
:no_upscale():strip_icc():strip_exif()/f/image/js4KyH6pmnajXmzjFtQxPy64.jpg?f=user_large)
:strip_exif()/f/image/wbNUfUnZK0mG0cJNUgvBoqqO.png?f=user_large)
/u/479384/crop58e4c54c15b23_cropped.png?f=community)
:strip_icc():strip_exif()/u/141725/crop5e995d7de93ef_cropped.jpeg?f=community)
:strip_icc():strip_exif()/u/2026/DSC_2281.jpg?f=community)
Daarmee wordt het product beter. Alleen als je dan opschrijft dat je vragen hebt over de werking van eco maar eigenlijk bedoeld dat je een feature request hebt komen we niet tot elkaar inderdaad. 
:strip_icc():strip_exif()/u/517178/crop5e8dca00a16b9.jpeg?f=community)
:strip_exif()/f/image/E7uvG4Qd6xIGtPNFihnKeT2H.png?f=user_large)
:strip_exif()/f/image/Ir0KAqz6tt0WyzO9cAi1Pb5y.png?f=user_large)
:strip_icc():strip_exif()/u/127354/crop60ad1df3adda4_cropped.jpg?f=community)
:strip_exif()/f/image/UibA0L8b1iBl3olMaDVmNOvS.png?f=user_large)
:strip_exif()/f/image/acmZYHlBru1VYgr23BsLmSfc.png?f=user_large)
:strip_exif()/f/image/wiEjMwWxynK7peHVkZQdBQux.png?f=user_large)
:strip_exif()/f/image/cSyVZ8s1rGD8A7mB5MLblnx3.png?f=user_large)
:strip_exif()/f/image/aBODO7i9JfvcDJY9qU7T5XYC.png?f=user_large)
:strip_exif()/f/image/XBKbFqpAVqRicTJjg9Dtz2SY.png?f=user_large)
:strip_exif()/f/image/uowJZfbPIW3kSiI3mo4qV12M.png?f=user_large)
:strip_icc():strip_exif()/u/240964/crop638c8f6bd8301_cropped.jpg?f=community)
:strip_exif()/f/image/C2XjtTzIUCZnzPK4Tw3qpnna.png?f=user_large)
![[Afbeelding]](https://tweakers.net/i/wyNSRtLMLn5DAR1ApgtS5JOk-7A=/fit-in/4920x3264/filters:max_bytes(3145728):no_upscale():strip_icc():strip_exif()/f/image/6y0h9oUaktHkHhkrEm383Jww.jpg?f=user_large){kind=link}