After going away and having a really good go at this, I have managed to achieve reading historic monthly summary records from long-term history.
Octopus Energy record meter consumption kWh, by half-hour intervals, from the time when the smart meter was first working with Octopus. This data exists forever, and can be retrieved back from Octopus using a simple API call. For consumption data this is better than trying to use Home Assistant.
The cost is more complicated. Basically, Octopus don’t record / provide the cost (even though the smart meter does hold the tariff, and works out the costs so as to show on the IHD).
The Octopus Integration brings in data from the smart meter (consumption) and also the account (tariff and billing), so we do have the total accumulated cost for ‘yesterday’ (and the current accumulated cost for ‘today’ if you have an Octopus Mini) as the sum of standing charge, unit charge, and VAT. This “yesterday cost” figure is updated by Octopus once per day, and is updated in HA by the integration usually during the morning of the day after.
In simple terms, Home Assistant Recorder keeps all the state values of all entity sensors for every value change and for 10 days, and then throws away the data. However, some data is now kept in a long-term statistics table. For sensors with numeric data (and a data type category), at purge time each day, the recorder looks at each hour (on the hour) and will record a maximum, minimum, and average value for sensors that change value during the hour, or just a state value for sensors that have not changed value during the hour.
If we look at the Octopus Energy Electricity Previous Accumulative Cost sensor in the history window, we can see the short term values in bold, and the long term statistics data going back as far as records have been kept. The long-term history is never purged.
Although we can now easily see this data in the history display, getting at it is more complicated. The ApexCharts card does have a facility to switch from using entity ‘history’ (short term) to using ‘statistics’ (long term) data. This works, and can also be grouped and summed by time period. Unfortunately, while you can group by hour, day, quarter and so on, month is not a grouping option. This is because a month is not a fixed time duration, as it can range from 28 to 31 days, and there is no way to ‘inspect’ the month part of the timestamp. I created a ‘monthly cost’ chart in ApexCharts, using group by 31 days, and this is ‘ok’ but not correct, since it is dividing the year up into 31 day blocks, not months.
For interest, the ApexCharts config I used here is:
type: custom:apexcharts-card
header:
show: true
title: Monthly Electricity Costs
show_states: true
graph_span: 12 months
span:
end: month
yaxis:
- min: 0
max: ~150
decimals: 0
apex_config:
tickAmount: 3
series:
- entity: >-
sensor.octopus_energy_electricity_xxxxxx_xxxxxx_previous_accumulative_cost
name: Electricity Cost
color: royalblue
type: column
group_by:
func: sum
duration: 31d
float_precision: 2
statistics:
type: state
period: day
align: end
As a learning exercise, my next experiment was to use SQL to directly read the HA statistics table. This I have done in Node-RED, using the node-sqlite node to connect to the local HA SQLite database (file). There is an SQL sensor integration, but this can only return one row, and a single primitive value into the entity sensor state, so it would work for ‘this month’ or ‘last month’ with the correct summary SQL query, but cannot return a table of monthly values.
We first need a flow to dig into the statistics_meta table, so as to find the correct electricity - previous (not current) - accumulated - cost entity and the associated id. This has to be run only once (or you can use something like the SQLite Web integration tool to inspect this table directly. Since not all entities are moved into long term statistics, this table holds a mapping from entity id to statistics id. In my case, my import electricity meter value has stats table id 115, and my export electricity meter value has stats table id 295.
I used the SQLite Web integration to inspect the tables and to develop a query to return the data as an array.
The regular Node-RED flow now uses an SQL query to read the statistics table, for the relevant entity sensor(s), and for a given number of days (I have pulled a full 400 days so as to be able to get a full year of billing calculations). The SQL has to get the date/time from the Unix timestamp, and I am looking for only times after 20:00 and before 23:00, so as to ensure the update has actually taken place and to avoid DST time change problems when the clocks change. Finally the table is sorted, one row per id/day is selected.
SQL query used is:
SELECT id, state, udate FROM
(SELECT metadata_id AS id, state, date(created_ts, "unixepoch") AS udate, time(created_ts, "unixepoch") AS utime
FROM "statistics"
WHERE metadata_id IN ($id1, $id2) AND unixepoch()-created_ts < 86400 * $days AND utime > "20" AND utime < "23"
ORDER BY udate, id, utime DESC)
GROUP BY id, udate
Then a bit of JSONata is used to knock this result array/table into shape, sum by month, generate the final timestamp/value array, and uplift to a Sensor attribute. SQLite has limited processing ability, and JSONata is a perfect tool for manipulating arrays.
All it then takes is a short and simple ApexCharts graph to show this. Here I am going for the full year - the SQL pulls 400 days of history, then the JSONata chops off anything before the 1st of the first full month. The final month (most recent) will naturally be a part month, and this value is presented as the state value of the sensor used.
In addition, I have pulled an approximation for the billing figures, based on a given billing day of the month. For, eg the 23rd, each monthly bill will be 23rd prior month to 22nd this month (as the day of the billing will be captured in the following months bill). This is not an exact match with Octopus figures since the billing actually occurs during the early morning, and uses a real-time meter reading that is not exactly at 00:00.
Here is a flex table card with a year of monthly billing figures.
This is never going to be 100% accurate if there are holes in the statistics record, but I think that it does do the job.
For anyone interested in trying this out, here are the Node-RED flows, the ApexCharts config, and the Flex-Table card configuration.
Note: I am using the SQLite node in ‘read only’ mode and with a ‘prepared statement’ to remove any opportunity to accidently write to the HA database.
Node-RED flows:
The usual rules apply - HA server has been removed and needs to be edited back in, you will need the sqlite-node added to the palette, and you may need to adjust the database file location.
You will need to find and use the correct id(s) and you will need to change your billing day.
The code is set to look for and process both import meter sensor and export meter sensor, so it may need modification to just work with a single import meter.
This is experimental only, but hopefully it provides ideas on how read long term data so as to sum Daily Energy Costs for the current and last months!
[{"id":"5bbb0a7b36aca3a5","type":"sqlite","z":"bf1c409ead401b04","mydb":"56ce7a19f8e148e7","sqlquery":"prepared","sql":"SELECT statistic_id, id FROM \"statistics_meta\"","name":"Long Term stats (name & id)","x":380,"y":540,"wires":[["e58ea3269b602950"]]},{"id":"4e16de31854624b6","type":"debug","z":"bf1c409ead401b04","name":"debug 375","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":950,"y":540,"wires":[]},{"id":"a95e2d5e0e9b789f","type":"inject","z":"bf1c409ead401b04","name":"Find id we want","props":[{"p":"payload"},{"p":"params","v":"{}","vt":"json"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":140,"y":540,"wires":[["5bbb0a7b36aca3a5"]]},{"id":"e58ea3269b602950","type":"change","z":"bf1c409ead401b04","name":"Filter electricity previous cost sensors","rules":[{"t":"set","p":"test_a","pt":"msg","to":"sensor","tot":"str"},{"t":"set","p":"test_b","pt":"msg","to":"electricity","tot":"str"},{"t":"set","p":"test_c","pt":"msg","to":"previous_accumulative_cost","tot":"str"},{"t":"set","p":"payload","pt":"msg","to":"payload[$contains(statistic_id, $$.test_a) and $contains(statistic_id, $$.test_b) and $contains(statistic_id, $$.test_c)]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":690,"y":540,"wires":[["4e16de31854624b6"]]},{"id":"7d75baa1440f9b09","type":"inject","z":"bf1c409ead401b04","name":"Get long-term stats","props":[{"p":"params","v":"{\"$id1\":115,\"$id2\":295,\"$days\":400}","vt":"json"},{"p":"billday","v":"23","vt":"num"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":150,"y":640,"wires":[["0a3e0b4f3b233945"]]},{"id":"0a3e0b4f3b233945","type":"sqlite","z":"bf1c409ead401b04","mydb":"56ce7a19f8e148e7","sqlquery":"prepared","sql":"SELECT id, state, udate FROM\n (SELECT metadata_id AS id, state, date(created_ts, \"unixepoch\") AS udate, time(created_ts, \"unixepoch\") AS utime\n FROM \"statistics\"\n WHERE metadata_id IN ($id1, $id2) AND unixepoch()-created_ts < 86400 * $days AND utime > \"20\" AND utime < \"23\"\n ORDER BY udate, id, utime DESC)\nGROUP BY id, udate","name":"Long term stats array","x":360,"y":640,"wires":[["2b5aa20e8b4d25e1"]]},{"id":"1d97f4c35c9c107b","type":"ha-sensor","z":"bf1c409ead401b04","name":"Octopus Monthly Costs","entityConfig":"07e11338ea1c60c6","version":0,"state":"payload.import.from","stateType":"jsonata","attributes":[{"property":"import_total","value":"payload.import.total","valueType":"msg"},{"property":"import_table","value":"payload.import.months.[timestamp, total]","valueType":"jsonata"},{"property":"export_total","value":"payload.export.total","valueType":"msg"},{"property":"export_table","value":"payload.export.months.[timestamp, total]","valueType":"jsonata"},{"property":"bill_table","value":"payload.bills","valueType":"msg"}],"inputOverride":"allow","outputProperties":[],"x":770,"y":640,"wires":[[]],"server":""},{"id":"2b5aa20e8b4d25e1","type":"change","z":"bf1c409ead401b04","name":"Analyse","rules":[{"t":"set","p":"payload","pt":"msg","to":"(\t/* count entities recorded - expect one import or two as import and export */ \t $ids:=$distinct(payload.id);\t $imp:=$ids[0]; /* import id */\t $exp:=$ids[1]; /* export id */\t $bill:=billday;\t\t/* add month and year for easy parsing */\t $base:=payload~>|$|(\t $x:=$split(udate,\"-\");\t {\"month\": $number($x[1]),\t \"day\": $number($x[2]),\t \"year\": $number($x[0])})|;\t\t/* get data end date, should be yesterday, and trim base array to one year */\t $last:=($base[id=$ids[0]].udate)[-1];\t $from:=$number($substringBefore($last,\"-\"))-1 & $substring($last,4,6);\t $year:=$base[udate>$from];\t $days:=$count($year)/$count($ids);\t \t/* compose analysis for all entities */\t $results:=$ids.(\t $id:=$;\t $array:=$year[id=$id];\t $year_total:=$sum($array.state)~>$round(2);\t $day_count:=$count($array);\t $start:=$array[day=1][0].udate;\t $month_array:=$array[udate>=$start];\t $table:=[1..12].(\t $m:=$;\t $month:=$month_array[month=$m];\t {\"month\": $m, \"year\": $month[0].year,\t \"timestamp\": $month[0].udate & \"T12:00:00\"~>$toMillis(),\t \"total\": $month.state~>$sum()~>$round(2)};\t );\t {\"id\": $id,\t \"from\": $from,\t \"base\": $month_array[0].udate,\t \"last\": $last,\t \"days\": $day_count,\t \"total\": $year_total,\t \"months\": $table^(timestamp)}\t );\t\t/* block by billing day, summing prior bill day inclusive to next bill day exclusive */\t/* array of billing dates over data period */\t $bdates:=$base[id=$imp and day=$bill].udate;\t $bills:=$bdates[[1..20]]#$mon.(\t $dnext:=$;\t $dfrom:=$bdates[$mon]; /* mon is already from 0 when starting map at 1 */\t $dupto:=$fromMillis($toMillis($dnext & \"T12:00:00.000\")-86400000)~>$substringBefore(\"T\");\t $period:=$base[udate>=$dfrom and udate<$dnext];\t $debit:=$period[id=$imp].state~>$sum()~>$round(2);\t $credit:=$period[id=$exp].state~>$sum()~>$round(2);\t {\"from\": $dfrom,\t \"upto\": $dupto,\t \"charge\": $debit,\t \"credit\": $credit,\t \"bill\": $round($debit-$credit,2)};\t \t );\t\t {\"import\": $results[id=$imp],\t \"export\": $results[id=$exp],\t \"bills\" : $bills\t };\t\t\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":560,"y":640,"wires":[["1d97f4c35c9c107b"]]},{"id":"56ce7a19f8e148e7","type":"sqlitedb","db":"/homeassistant/home-assistant_v2.db","mode":"RO"},{"id":"07e11338ea1c60c6","type":"ha-entity-config","server":"","deviceConfig":"","name":"SC Oct Month Costs","version":"6","entityType":"sensor","haConfig":[{"property":"name","value":"Octopus Monthly Costs"},{"property":"icon","value":""},{"property":"entity_picture","value":""},{"property":"entity_category","value":""},{"property":"device_class","value":""},{"property":"unit_of_measurement","value":""},{"property":"state_class","value":""}],"resend":true,"debugEnabled":false}]
ApexCharts Card:
type: custom:apexcharts-card
header:
show: true
title: Monthly Electricity Values GBP
show_states: true
graph_span: 11 months
span:
end: month
offset: '-1month'
yaxis:
- min: 0
max: ~150
decimals: 0
apex_config:
tickAmount: 3
series:
- entity: sensor.octopus_monthly_costs
data_generator: return entity.attributes.import_table
name: Import Cost
float_precision: 2
color: green
type: column
- entity: sensor.octopus_monthly_costs
data_generator: return entity.attributes.export_table
name: Export Return
float_precision: 2
color: red
type: column
Flex Table Card:
type: custom:flex-table-card
title: Octopus Monthly Billing
entities:
include: sensor.octopus_monthly_costs
columns:
- data: bill_table
modify: x.upto
name: Billing Date
- data: bill_table
modify: x.from
name: From (inc)
- data: bill_table
modify: parseFloat(x.charge).toFixed(2)
align: right
name: Charge
- data: bill_table
modify: parseFloat(x.credit).toFixed(2)
align: right
name: Credit
- data: bill_table
modify: parseFloat(x.bill).toFixed(2)
align: right
name: Bill
For just the current month total figure, the entity sensor attribute state value could be set as
payload.import.months[-1].total
or [-2] for the last month total figure.
