The Dutch Windcentrale app has stopped working due to a new API. More generic learnings for larger audience:
- Create cumulative SQL result
- Get multi-result SQL query data to HA sensor, and plot with ApexCharts
- Create static benchmark series sensor
UPDATED, SEE NEW POST BELOW
My favorite graph (in the now defunct app) is this monthly cumulative graph, with benchmark line:
I have mimicked this graph in Home Assistant. I now only hope that @jobvk is able to transform his custom component to the new API
Result:
Prerequisites
- ApexCharts (from HACS)
- Your data in a database. I use LTSS but you could also query the long term statistics database built in HA. I have also applied the
state_numericadditional column in the database. - NodeRed addon
- Benchmark data. I have 3 shares; you can scale my prognosis data (see below)
1 Query the database
Data is stored as cumulative per month; it resets every month. What we need is cumulative per year, in monthly buckets. This query picks up the data and sums the maximum values of each month to date:
WITH data AS (
SELECT DISTINCT ON (date_trunc('month', time)) date_trunc('month', time) as Month_Name, time, state_numeric, MAX(state_numeric) as maxx
FROM ltss WHERE entity_id='sensor.het_rode_hert_month_production' AND state_numeric IS NOT NULL AND extract (year FROM time) = extract (year FROM CURRENT_DATE)
GROUP BY date_trunc('month', time), state_numeric, time
ORDER BY date_trunc('month', time),state_numeric DESC
)
SELECT time, SUM(state_numeric) OVER (ORDER BY time ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS state_numeric
FROM data
2 Get the data via Node Red
I am querying the data through Node Red with this flow. It does the following:
- Executes the SQL query.
- Ads a random number to
msg.random - jsonify
msg.payload - Sets state of a HA sensor to
msg.randomand sets attributedatatomsg.payload. Reason for this is the limit to number of characters of 256 for states.
NodeRed code
[{"id":"2c7a814593cededa","type":"tab","label":"Winddelen","disabled":false,"info":"","env":[]},{"id":"101b5edb1fad37ad","type":"postgresql","z":"2c7a814593cededa","name":"","query":"with data as (\n\n\nSELECT DISTINCT ON (date_trunc('month', time)) \n date_trunc('month', time) as Month_Name, time, state_numeric,\n MAX(state_numeric) as maxx\nFROM ltss WHERE entity_id='sensor.het_rode_hert_month_production' AND state_numeric IS NOT NULL AND \n extract (year FROM time) = extract (year FROM CURRENT_DATE)\n\n GROUP BY date_trunc('month', time), state_numeric, time\nORDER BY date_trunc('month', time),state_numeric DESC)\n\nselect time, sum(state_numeric) over (order by time asc rows between unbounded preceding and current row) as state_numeric\nfrom data","postgreSQLConfig":"36b36b8f4f678a27","split":false,"rowsPerMsg":1,"outputs":1,"x":170,"y":140,"wires":[["e8eb0a23da70f150"]]},{"id":"085f426f3a424c7f","type":"debug","z":"2c7a814593cededa","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":470,"y":340,"wires":[]},{"id":"94fcb0d10916e074","type":"inject","z":"2c7a814593cededa","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":100,"y":240,"wires":[["101b5edb1fad37ad"]]},{"id":"d5ed69168f298e96","type":"ha-entity","z":"2c7a814593cededa","name":"winddelen maandproductie2","server":"c5f56a3a.f3f838","version":2,"debugenabled":true,"outputs":1,"entityType":"sensor","config":[{"property":"name","value":""},{"property":"device_class","value":""},{"property":"icon","value":""},{"property":"unit_of_measurement","value":"x"},{"property":"state_class","value":""},{"property":"last_reset","value":""}],"state":"random","stateType":"msg","attributes":[{"property":"data","value":"payload","valueType":"msg"}],"resend":true,"outputLocation":"payload","outputLocationType":"none","inputOverride":"allow","outputOnStateChange":false,"outputPayload":"","outputPayloadType":"str","x":520,"y":240,"wires":[[]]},{"id":"815019d70363da9c","type":"json","z":"2c7a814593cededa","name":"","property":"payload","action":"str","pretty":false,"x":290,"y":240,"wires":[["085f426f3a424c7f","d5ed69168f298e96"]]},{"id":"e8eb0a23da70f150","type":"random","z":"2c7a814593cededa","name":"","low":1,"high":"100","inte":"true","property":"random","x":380,"y":140,"wires":[["815019d70363da9c"]]},{"id":"36b36b8f4f678a27","type":"postgreSQLConfig","name":"","host":"192.168.0.5","hostFieldType":"str","port":"5432","portFieldType":"num","database":"ha_ltss","databaseFieldType":"str","ssl":"false","sslFieldType":"bool","applicationName":"","applicationNameType":"str","max":"10","maxFieldType":"num","idle":"1000","idleFieldType":"num","connectionTimeout":"10000","connectionTimeoutFieldType":"num","user":"homeassistant","userFieldType":"str","password":"","passwordFieldType":"str"},{"id":"c5f56a3a.f3f838","type":"server","name":"Home Assistant","version":2,"addon":true,"rejectUnauthorizedCerts":true,"ha_boolean":"y|yes|true|on|home|open","connectionDelay":true,"cacheJson":true,"heartbeat":false,"heartbeatInterval":"30"}]
3 Add Prognosis sensor
Add this to your configuration.yaml. You will need to update the dates yearly. This is for 3 shares; scale for your own situation.
- platform: template
sensors:
winddelen_prognose:
friendly_name: Winddelen Prognose
unit_of_measurement: 'kWh'
value_template: 1
attribute_templates:
prognose: |
{{ [
{ "date" : "2022-01-01","value" : 0},
{ "date" : "2022-01-31","value" : 178},
{ "date" : "2022-02-28","value" : 311},
{ "date" : "2022-03-31","value" : 461},
{ "date" : "2022-04-30","value" : 566},
{ "date" : "2022-05-31","value" : 669},
{ "date" : "2022-06-30","value" : 745},
{ "date" : "2022-07-31","value" : 836},
{ "date" : "2022-08-31","value" : 915},
{ "date" : "2022-09-30","value" : 1020},
{ "date" : "2022-10-31","value" : 1150},
{ "date" : "2022-11-30","value" : 1330},
{ "date" : "2022-12-31","value" : 1500}
] }}
4 Add graph in HA
Add an ApexCharts card to your dashboard. It extracts data from the prognosis sensor and from the SQL sensor. The latter is de-jsonified in-situ.
type: custom:apexcharts-card
graph_span: 365d
span:
start: year
header:
show: false
all_series_config:
stroke_width: 2
unit: kWh
apex_config:
xaxis:
type: categor
overwriteCategories:
- jan
- feb
- mar
- apr
- mei
- jun
- jul
- aug
- sep
- okt
- nov
- dec
tickPlacement: between
labels:
datetimeFormatter:
month: MMM
tickAmount: 12
chart:
background: '#3f8bc9'
foreColor: '#ffffff'
markers:
size: 1
series:
- entity: sensor.winddelen_prognose
type: line
curve: straight
color: '#999999'
data_generator: |
return entity.attributes.prognose.map((entry) => {
return [new Date(entry.date), entry.value];
});
- entity: sensor.nodered_d5ed69168f298e96
type: line
curve: straight
color: '#ffffff'
extend_to: now
data_generator: |
const data = JSON.parse(entity.attributes.data);
return data.map((entry) => {
return [new Date(entry.time), entry.state_numeric];
});
5 To Do
- Automate retrieval of Windcentrale data
- Schedule execution of query
- Find out how to make sensor name in NodeRed pretty
