Rest Sensor (external API resource) with historical data - how to graph?

[tedted]

I’m trying to graph historical energy usage from a smart meter API (n3rgy).

Here’s the JSON received when making the rest call:

{
  "resource": "/electricity/consumption/1",
  "responseTimestamp": "2021-06-04T11:23:42.027Z",
  "start": "202106030030",
  "end": "202106040000",
  "granularity": "halfhour",
  "values": [
    {
      "timestamp": "2021-06-03 00:30",
      "value": 0.176
    },
    {
      "timestamp": "2021-06-03 01:00",
      "value": 0.177
    },
    {
      "timestamp": "2021-06-03 01:30",
      "value": 0.175
    },
    {
      "timestamp": "2021-06-03 02:00",
      "value": 0.16
    }
  ],
  "availableCacheRange": {
    "start": "202102051430",
    "end": "202106030400"
  },
  "unit": "kWh"
}

(it provides the previous day’s usage, in 30 minute blocks.

I simply want to get this data in to HA so I can graph it, in 30 minute slots with the values as per the JSON.

Here’s what I have so far:

  - platform: rest
    resource: https://consumer-api.data.n3rgy.com/electricity/consumption/1
    name: Electricity Usage
    scan_interval: 86400
    headers:
      Authorization: abc123
    value_template: 'OK'
    json_attributes:
      - values

The data is returned and now stored in the sensor, but what do I need to actually plot the data in a simple bar chart?

Thanks,

Ted

[Troon]

Storing values with non-current timestamps is not really something HA does. You’d be better off reading the current value every half hour, if that’s possible, then plotting how you choose. Octopus Agile in my region, doing exactly that:

[tedted]

Thanks for the reply. Unfortunately my provider (OVO) doesn’t provide an API, and only shows the previous days usage in their own UI. So I’m using the third party n3rgy to grab this raw data from the data my meter is sending, and pull in to HA via the rest sensor.

I was hoping to run the sensor once per day, after midnight, to then store the previous days usage in the 30 minutes chunks, so over time, I’ll have all the data in 30 minute chunks to compare against etc.

Hmm… any way to “insert” data to HA for historical data points?

[mcaddy (Mike)]

Not sure if your still working on this but I stumbled across this post whilst trying to work out how to use a HACS integration (https://github.com/smartechru/n3rgy). I’ve yet to find out the right combination of settings, how is your progress?

[anthony1 (Anthony)]

i looked at that HACS integration and it seems to be aimed to the paid market not the free consumer service

[astronaut63]

I have created a node red flow that connects to n3rgy early each morning and pulls back the last 24 hours of data. It writes each 30 minute reading directly into influxdb with the correct timestamp (and grafana can then pull this out)
It also sums the elec consumption, export and gas consumption into 3 separate HA sensors e.g. yday_elec_in. The MAC address of your IHD is configured in the first node (the inject timer)

[{"id":"77496a2a.c76484","type":"tab","label":"Flow 2","disabled":false,"info":""},{"id":"bea6e51f.bebff8","type":"inject","z":"77496a2a.c76484","name":"","props":[{"p":"headers.Authorization","v":"yourIHDMAC","vt":"str"}],"repeat":"","crontab":"15 06 * * *","once":false,"onceDelay":0.1,"topic":"","x":160,"y":220,"wires":[["4382bbc1.39bbd4","d4803192.2ef7b","1deddef.40a9f21"]]},{"id":"4382bbc1.39bbd4","type":"http request","z":"77496a2a.c76484","name":"http elec used","method":"GET","ret":"obj","paytoqs":"ignore","url":"https://consumer-api.data.n3rgy.com/electricity/consumption/1","tls":"","persist":false,"proxy":"","authType":"","x":370,"y":220,"wires":[["44e170ed.28da6","1f305bc2.f76894"]]},{"id":"d4803192.2ef7b","type":"http request","z":"77496a2a.c76484","name":"http gas used","method":"GET","ret":"obj","paytoqs":"ignore","url":"https://consumer-api.data.n3rgy.com/gas/consumption/1","tls":"","persist":false,"proxy":"","authType":"","x":360,"y":340,"wires":[["28d6fb7c.1b8e14"]]},{"id":"1deddef.40a9f21","type":"http request","z":"77496a2a.c76484","name":"http elec generated","method":"GET","ret":"obj","paytoqs":"ignore","url":"https://consumer-api.data.n3rgy.com/electricity/production/1","tls":"","persist":false,"proxy":"","authType":"","x":370,"y":420,"wires":[["4dc11d0.8103de4"]]},{"id":"44e170ed.28da6","type":"function","z":"77496a2a.c76484","name":"","func":"var i = 0;\nvar total = 0;\nlet temp = [];\nvar value_list = [];\nvar status_list = [];\nvar temptime = \"\"\nvar msgout1 = {};\nvar msgout2 = {};\nfor (i=0; i<  msg.payload.values.length; i++)  {\n temptime =  msg.payload.values[i].timestamp;\n if (temptime.length==16) {\n     temptime = temptime.replace(\" \",\"T\") + \":00.000Z\";\n }\n temp.push(\n {\n measurement: \"Metered_Energy\",\n fields:  {\n kWh_in: msg.payload.values[i].value\n },\n tags: {\n type: \"electricity_consumed\"      \n },\n timestamp:  Date.parse(temptime)*1000000\n }     \n )    \ntotal = total + msg.payload.values[i].value;    \n}\n\nif ( msg.payload.values.length == 48) {\n    msgout2.payload = total;}\nelse {\n    msgout2.payload = msg.payload.values.length\n}  \n\n \nmsgout1.payload = temp;\nreturn [msgout1, msgout2];","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":600,"y":260,"wires":[["add86f5a.cefa"],["87f197e7.c5d868"]]},{"id":"add86f5a.cefa","type":"influxdb batch","z":"77496a2a.c76484","influxdb":"1aba0ec9.6819b1","precision":"","retentionPolicy":"","name":"homeassistant_daily","database":"database","precisionV18FluxV20":"s","retentionPolicyV18Flux":"","org":"homeassistant","bucket":"bucket","x":930,"y":360,"wires":[]},{"id":"28d6fb7c.1b8e14","type":"function","z":"77496a2a.c76484","name":"","func":"var i = 0;\nvar total = 0;\nlet temp = [];\nvar temptime = \"\"\nvar msgout1 = {};\nvar msgout2 = {};\nfor (i=0; i<  msg.payload.values.length; i++)  {\n temptime =  msg.payload.values[i].timestamp;\n if (temptime.length==16) {\n     temptime = temptime.replace(\" \",\"T\") + \":00.000Z\";\n }\n temp.push(\n {\n measurement: \"Metered_Energy\",\n fields:  {\n m3: msg.payload.values[i].value\n },\n tags: {\n type: \"gas_consumed\"      \n },\n timestamp:  Date.parse(temptime)*1000000\n }     \n )    \ntotal = total + msg.payload.values[i].value;    \n}\n\nif ( msg.payload.values.length == 48) {\n    msgout2.payload = total;}\nelse {\n    msgout2.payload = 0\n}  \n\n \nmsgout1.payload = temp;\nreturn [msgout1, msgout2];","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":610,"y":320,"wires":[["add86f5a.cefa"],["867101a9.0a90f"]]},{"id":"4dc11d0.8103de4","type":"function","z":"77496a2a.c76484","name":"","func":"var i = 0;\nvar total = 0;\nlet temp = [];\nvar temptime = \"\"\nvar msgout1 = {};\nvar msgout2 = {};\ntry{\nfor (i=0; i<  msg.payload.values.length; i++)  {\n temptime =  msg.payload.values[i].timestamp;\n if (temptime.length==16) {\n     temptime = temptime.replace(\" \",\"T\") + \":00.000Z\";\n }\n temp.push(\n {\n measurement: \"Metered_Energy\",\n fields:  {\n kWh_out: msg.payload.values[i].value\n },\n tags: {\n type: \"electricity_exported\"      \n },\n timestamp:  Date.parse(temptime)*1000000\n }     \n )    \ntotal = total + msg.payload.values[i].value;    \n}\n\nif ( msg.payload.values.length == 48) {\n    msgout2.payload = total;}\nelse {\n    msgout2.payload = 0\n}  \n\n \nmsgout1.payload = temp;\nreturn [msgout1, msgout2];\n\n} catch (e) {\n    return null\n}","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":610,"y":460,"wires":[["add86f5a.cefa"],["6ba7217d.e6764"]]},{"id":"1f305bc2.f76894","type":"debug","z":"77496a2a.c76484","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":580,"y":160,"wires":[]},{"id":"87f197e7.c5d868","type":"ha-entity","z":"77496a2a.c76484","name":"yday_elec_in","server":"d0eba65d.81fba8","version":1,"debugenabled":false,"outputs":1,"entityType":"sensor","config":[{"property":"name","value":"yday_elec_in"},{"property":"device_class","value":""},{"property":"icon","value":""},{"property":"unit_of_measurement","value":"kWh"}],"state":"payload","stateType":"msg","attributes":[],"resend":true,"outputLocation":"","outputLocationType":"none","inputOverride":"allow","outputOnStateChange":false,"outputPayload":"$entity().state ? \"on\": \"off\"","outputPayloadType":"jsonata","x":910,"y":420,"wires":[[]]},{"id":"867101a9.0a90f","type":"ha-entity","z":"77496a2a.c76484","name":"yday_gas_in","server":"d0eba65d.81fba8","version":1,"debugenabled":false,"outputs":1,"entityType":"sensor","config":[{"property":"name","value":"yday_gas_in"},{"property":"device_class","value":""},{"property":"icon","value":""},{"property":"unit_of_measurement","value":"m3"}],"state":"payload","stateType":"msg","attributes":[],"resend":true,"outputLocation":"","outputLocationType":"none","inputOverride":"allow","outputOnStateChange":false,"outputPayload":"$entity().state ? \"on\": \"off\"","outputPayloadType":"jsonata","x":910,"y":480,"wires":[[]]},{"id":"6ba7217d.e6764","type":"ha-entity","z":"77496a2a.c76484","name":"yday_elec_out","server":"d0eba65d.81fba8","version":1,"debugenabled":false,"outputs":1,"entityType":"sensor","config":[{"property":"name","value":"yday_elec_out"},{"property":"device_class","value":""},{"property":"icon","value":""},{"property":"unit_of_measurement","value":"kWh"}],"state":"payload","stateType":"msg","attributes":[],"resend":true,"outputLocation":"","outputLocationType":"none","inputOverride":"allow","outputOnStateChange":false,"outputPayload":"$entity().state ? \"on\": \"off\"","outputPayloadType":"jsonata","x":920,"y":540,"wires":[[]]},{"id":"300cbfb6.a5169","type":"debug","z":"77496a2a.c76484","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":910,"y":660,"wires":[]},{"id":"1aba0ec9.6819b1","type":"influxdb","hostname":"127.0.0.1","port":"8086","protocol":"http","database":"homeassistant","name":"homeassistant","usetls":false,"tls":"78605c47.6ab364","influxdbVersion":"1.x","url":"http://localhost:8086","rejectUnauthorized":true},{"id":"d0eba65d.81fba8","type":"server","name":"Home Assistant","version":1,"addon":true,"rejectUnauthorizedCerts":true,"ha_boolean":"y|yes|true|on|home|open","connectionDelay":true,"cacheJson":true},{"id":"78605c47.6ab364","type":"tls-config","name":"","cert":"","key":"","ca":"","certname":"","keyname":"","caname":"","servername":"","verifyservercert":false}]

[vegetablesalad (Valts)]

I have the exact same issue. My provider gives me API where I can get previous days usage data. I believe it reads meter only once in 24h around midnight. Anyone has stumbled upon a solution that doesn’t write directly into DB. That sounds bit too much for me.

[petro]

There won’t be unless someone makes a custom card that displays non-historical data. You’ll always have to modify the db because home assistant expects the data to be sent one at a time. It would be best to investigate this from the other side, to get it to display the data piece meal instead of a lump at the end of the day.

[koying (Chris B)]

With apexchart, you can feed data from anywhere as long as you can create a javascript function that can fetch it.

See below for an example from InfluxDB, which can be reused for any kind of REST source
To be crystal clear: Nothing is written nor usable in HA. This is purely to display a graph in Lovelace

Using HA or HACS graphs w/ InfluxDB data in HA?

Just for fun (yeah, I know, I have a strange notion of fun), I tried to use ApexCharts-Card directly with influxdb. That card allows to provide javascript code as data source. It doesn’t work out-of-the box, because of async issues, but I managed to make it work without going through the recorder at all after that PR CHG: make datagenerator async by koying · Pull Request #157 · RomRider/apexcharts-card (github.com) EDIT: The change is in release 1.9.0 type: 'custom:apexcharts-card' graph_span:…

[aryankids (Amir Aryanpour)]

@astronaut63 thanks for the solution. I incorporated it in the HA. However, have you ever tried to push this to the Static table of Energy Dashboard to use that instead of Grafana?

[mcaddy (Mike)]

I’ve seen references to the fact that HA can’t take in timestamped data, however I was taking a look at the Energy dashboard and it got me thinking about the future solar projection I’m seeing.

How does HA manage to render a plot for tomorrows solar predictions? Might it be possible to use the same technique to load past electric consumption?

Screen shot taken shortly after 16:00, but data already exists on the dotted line for 17:00? just in case someone thinks this might be due to hour offset or time zone, all of tomorrows predictions are also plotted in the energy dashboard.

[petro]

That’s just the plot of data using trend lines. It’s not adding data into the database at a future date.

[mcaddy (Mike)]

If it is a trend line I would have expected the peak to be closer to the left of the graph?

And If it is a trend line, how do you explain, tomorrows forecast data already existing?

[petro]

It’s using past data to determine a future outcome, i.e. a forecast.

[petro]

Maybe you are miss interpreting what I’m saying with the word trendline. A trendline is just a term used to fit a smooth line to a data set. In this instance, that data set is a prediction of the where your energy might be at that time based on past data.

[mcaddy (Mike)]

I assumed you meant that the dotted line was a trend line for the Yellow bars, that isn’t what I’m seeing. Hence the confusion.

The Solar Edge integration appeared to be the source of the data? Are you saying that the projection of tomorrows prediction is done by the HA Energy interface and it isn’t being provided by the SolarEdge integration? The fact that all the points on the graph had a discrete value led me to believe that it was a render of data, presumably something stored in the system?

[petro]

@mcaddy

The Solar Edge integration appeared to be the source of the data?

If that entity comes from solar edge, then yes.

@mcaddy

Are you saying that the projection of tomorrows prediction is done by the HA Energy interface and it isn’t being provided by the SolarEdge integration?

It’s provided by previous data stored in the home assistant database, which was (in the past) supplied by the SolarEdge integration.

@mcaddy

The fact that all the points on the graph had a discrete value led me to believe that it was a render of data, presumably something stored in the system?

It’s a render of past data as a prediction of a future outcome. For example, every tuesday @ 6pm you turn on a lamp for 1 hour which makes a 1 hour energy spike. Well, a good prediction would be that, this Tuesday at 6pm, you’ll see an upward bump in your prediction trendline.

No new data is being created. It’s a prediction based on past data.