Yes, Home Assistant is the electronic equivalent of a garden shed or allotment, although it does not actually get you out of the house.
Glad to hear that you managed to get it all working. This discussion is really about graphing Octopus Energy integration, but you may like to post your Node-RED flow, sensor, and card configuration for others to learn from. I would suggest posting a new discussion, tagged as Node-RED as NR is quite a niche area for HA users.
Thanks, that’s really helpful.
Ended up using something similar to this, along with a utility meter to show exactly what I was after.
For my instance, the start and end components of the charge array fluctuates between 00:00 yesterday and 00:00 the day before yesterday.
Is there a way to calculate the span.offset value of the Apexcharts-card based on the actual values of the first start component of the charge array attribute?
As is typical with the gas readings, they will generally be for the-day-before-yesterday up until around early evening, when they change to those for yesterday. This is indeed challenging if you want to show them regardless of which day.
As far as I recall, the span and offset settings can only be hard literals, and thus passing variables to these settings is either not possible, or requires a template card to hold the graph card and process the variable settings.
I believe that it is easier to expand the graph to span 48 hours and set the offset to -2days, which means it shows the-day-before-yesterday through to yesterday, hence the-day-before-yesterday will show, as will yesterday. You just see either day-empty or empty-day accordingly (and will thus know which day’s worth you are looking at).
Alternatively, a trick is to modify the data by adding extra day(s) as required. This can be done inside the data generator.
type: custom:apexcharts-card
header:
show: true
title: Octopus Gas kWh - Previous 'Day' Consumption
show:
last_updated: true
graph_span: 24h
span:
start: day
series:
- entity: >-
sensor.octopus_energy_gas_xxxxx_xxxxx_previous_accumulative_consumption_kwh
data_generator: |
const array = entity.attributes.charges;
let days = (Date.now() - new Date(array[0].start).getTime())/86400000;
let delta = Math.floor(days)*86400000;
let final = [];
let i, j=0;
for (i=0; i<array.length-1; i=i+2){
final[j] = [(new Date(array[i].start).getTime() +delta), array[i].consumption_kwh + array[i+1].consumption_kwh];
j++;
};
return final;
type: column
color: green
unit: kWh
This will shift the-day-before-yesterday up by two days, and yesterday up by one day, thus making either today. Of course, this does not tell you which day you are looking at, and it will still fail if the Gas readings are not updated by the end of the day (which does happen).
Thanks for your detailed answer. It is tremendous helpful.
Nevermind about the actual day. It is not shown on the card anyway.
I would like to share my code based on your code. I utilized the variable start provided by the card to make the chart always show from the beginning. Assuming it always start from midnight, the time axis should be correct. I don’t group the data into hour myself but the following does as yours. Furthermore, I prefer reporting in Wh as KWh is quite a big unit for my usage.
type: custom:apexcharts-card
header:
show: true
title: Octopus Electricity Consumption - Previous 'Day' Consumption
show:
last_updated: true
graph_span: 24h
span:
start: day
series:
- entity: >-
sensor.octopus_energy_electricity_xx_yy_previous_accumulative_cost
data_generator: |
const array = entity.attributes.charges;
let delta = (start - new Date(array[0].start).getTime());
let final = [];
let i, j=0;
for (i=0; i < array.length; i=i+1){
final[j] = [
(new Date(array[i].start).getTime() + delta),
array[i].consumption * 1000
];
j++;
};
return final;
name: Electricity Consumption
unit: Wh
type: column
color: Purple
group_by:
func: sum
duration: 1h
A neater solution than mine!
I would like to plot a graph of Calorific value per date as well.
with the following attributes of Entity Current Accumulative Cost Gas (??/??)
Mprn xxx
Serial number yyy
Tariff code zzz
Standing charge 0.3
Total without standing charge 0.59
Total 0.89
Charges - start: '2024-12-14T12:30:00+00:00' end: '2024-12-14T13:00:00+00:00' rate: 0.057192 consumption: 6.41821 cost: 0.37 - start: '2024-12-14T13:00:00+00:00' end: '2024-12-14T13:30:00+00:00' rate: 0.057192 consumption: 3.916 cost: 0.22
Calorific value 39.3
How to do it?
I have solved the above problem by using a helper template.
The state template is defined as:
{% set value = state_attr('sensor.octopus_energy_gas_xxx_xxx_current_accumulative_cost', 'calorific_value') %}
{{ value | float if value | is_number else this.state }}
Calorific value is the regional value published by the gas supplier as the extractable energy in megajoules per cubic metre of gas delivered. This changes only slightly and infrequently over the year, due to changes in the constituent composition of the gas. In fact, the gas suppliers do everything to keep this value constant.
To capture this attribute over time earlier than the 10 days default short-term record would require a daily automation to record the value into a file somewhere. Easy enough to do using Node-RED.
Of course, since the bill is calculated on the basis of cubic meterage used multiplied by calorific value divided by megajoule to kWh conversion, you could always work backwards from your daily total energy kWh used and total volume m3 consumption figures.
To calculate a billable kWh, starting with cubic meters consumed:
Step 3
This figure is then multiplied by the calorific value of the gas which is a measure of the available heat energy. Calorific values vary and the figure quoted on your bill (eg 39.5 megajoules per cubic metre (MJ/m³)) will be an average of the gas supplied to your property (the regulations explain how this is calculated). Gas transporters are required to maintain this figure within 38 MJ/m³ to 41 MJ/m³ as figures outside of this range will cause problems with gas burning appliances.
Step 4
The figure is then multiplied by 1.02264 as prescribed in the above regulations. This corrects the volume of gas to account for temperature and pressure (as gas expands and contracts).
Step 5
Finally the figure is converted to kWh by dividing by 3.6. Again, this factor is prescribed in the above regulations.
If my maths is correct, the reverse is:
Calorific Value (MJ/m3) =
Energy used (kWh) x 3.6 (MJ/kWh)
----------------------------------------
Volume used (m3) x 1.02264
If you use a computation like this, as long as you have a history of daily totals for energy billed (kWh) and gas consumption (m3) you can back-calculate the Calorific Value. As an amusing little exercise, I have (using Node-RED) extracted the long-term statistics state values for my previous gas kwh by day, along with previous gas m3 by day, and performed the above calculation for each day.
The result, rounded to 1dp, is 38.7, and is exactly the same every day since last May when the integration first provided separate entities for gas m3 and kwh. Also, my current Octopus Energy Integration gas entities are showing Calorific Value as 38.7, so at least that agrees.
However, when I look at my recent bill from Octopus, the Calorific Value (both stated in the document and as actually used in the monthly calculation to arrive at my kWh from m3) is 39.0
Given that Smart Gas Meters record only in cubic meters, something somewhere is working out the kWh based on a Calorific Value figure, and clearly Octopus are using 39.0 to bill me, and the integration is using and reporting 38.7.
Interesting 
Off to check the integration in GitHub, and I find:
When calculating gas costs, a calorific value is included in the calculation. Unfortunately this changes from region to region and is not provided by the Octopus Energy API. The default value of this is
40, but if you check your latest bill you should be able to find the value for you. This will give you a more accurate consumption and cost calculation when your meter reports inm3.
So there we have it. The Calorific Value used by the integration to compute kWh (and thus costs) based on the accurate m3 volume used, varies by region and is not provided by the Octopus Energy API. I surmise that the integration uses a constant approximate figure, and hence the Calorific Value, kWh, and Cost figures are all approximates only.
Go figure!
Edit
The Calorific Value (used to calcluate the cost from the m3 volume used) is set in the integration configuration.
For accurate results, this needs to be manually updated from the figure provided on the monthly energy bill (yes it is there in the gas calculation explanation…)
Thanks for your detailed explanation. The Octopus Energy integration has just released an update and it mentioned correcting the inconsistency of calorific value. Let’s hope it has fixed the issue.
It has been so long (my excuse) since I last set the Octopus Energy Integration configuration settings that I completely missed the fact that the Calorific Value is set there. 
Duh!
I had 38.7 in my settings, which I must have set up ages ago, and it is now 39.0.
Seems that I need to read the monthly energy statement, and if necessary update the Calorific Value in the configuration settings manually.
I use the Octopus plugin, a home mini and am on the intelligent octopus go tariff.
I’ve noticed on the energy graph that the accumulative consumption from the octopus plugin is only shown on the graph between 05:30 and 23:30 - exactly the times for the normal “peak” of IOG - the “off-peak” is not plotted.
If I look at the accumulative consumption data, it is tracking the data correctly, so I can’t see why the energy graph isn’t displaying the data correctly.
Any ideas anyone?
Alex
Hey folks
Sorry for dragging up an old post. I’ve only just started on my HA journey and I’m looking at Geoff’s method for pulling 12 months of data for Octopus electricity. My intention is to create several similar flows for representing different timescales and datasets for both gas an electricity.
The main issue I have right now is getting the flow Geoff posted (obviously modified with my own API call values) returned to HA.
From various other posts (mainly from Geoff, many in this thread) I’ve tried setting up a sensor and configured the entity-config and device-config. However, I (mis?)understand I can’t just pass msg.payload back to HA in an attribute because it is an object and not a simple data type. What then, is the best approach to return the data to HA with the intention of plotting in Apex-Charts? Do I need to create a sensor for each of the 12 months and return e.g. msg.payload[0].consumption / period etc? Or am I way off course?
So far, I’ve only added and configured the sensor after the posted flow
I don’t know if I’m looking in the right place but I’ve not found any documentation to tell me how to exactly this?
Thanks for your time and any advice
BBz
Home Assistant entities can only hold primitive data in the state value. Therefore, if you have 12 months of ‘a value’ it would take 12 different sensors to pass this to HA. That is a lot of sensors for a graph!
However, entity attributes can hold practically anything (up to a big limit). I pass quite complex objects, arrays, and arrays of objects by using the attributes. An array of 12 objects, each filled with the values I want, can go directly to HA.
I have found, the hard way, that for graphing stuff the trick is to pre-process arrays for Apex charts, so they arrive in a format that is easy to deal with. For Apex Charts the expectation is that you will use a data-generator to unpick the array (in the entity attribute) and map to an array of [timestamp, value] pairs.
Here is a pic of what I am working on at the moment - a graph of the sun path and horizon for my solar PV.
The data array going into an attribute-
Any attribute could be just msg.payload, or anything you want!
Naturally, if the main thing you want to pass is an array, and this goes into an attribute, the sensor state value will still need something in it - and if msg.payload has array in it, and you leave the default State set to msg.payload - BANG!
So, I always set the State to a primitive value of something of some use. For testing you can just set the State field option to ‘string’ and enter (without the quotes) “hello world”
After that there will be many more questions (not least on how to get Apex Charts working!) No, I don’t think there is much written up on this, but there are examples that you can pick through.
Oh wow, many thanks Geoff, I’m now getting data into an attribute in HA. I just didn’t have my head around the fact that State can be anything I like (was thinking I needed a state from the retrieved data) which had me stuck for a good while.
You (and others) have pasted plenty of examples of Apex Data Generator and of course there is documentation for this so I’ll have a good stab at it and see how I get on.
Thanks again for your very prompt and useful reply to my question - you’re a star!!
BBz
I know this thread is dated but…
How can I get a daily usage graph with the date logged correctly?
Thanks in advance.
Well hello again. Yes the thread is very dated, and I thought that you had cracked all of this with some neat coding last year 
.
Home Assistant is an entity-state machine and spends most of its energy dealing with ‘now’. History is limited, so I think that there are three ways to generate an historical daily-consumption graph [you don’t say over what period, but I assume calendar day total for, say the past 90 days].
I have worked on all three approaches, but now like using the long-term stats. API calls to Octopus can return the half-hour consumption/cost values for any period, but getting one year of readings returns over 17,500 records. Dealing with this quantity of data can be interesting.
The LT stats are there. BottleCap Dave’s integration generates sensor(s) for the previous consumption, and this goes into LT stats so it can be retrieved for any period beyond the default 10 days. Yes, Apex-charts will by default plot any entity sensor from history, however this is ‘history’ and not ‘long term statistics’ hence just plotting the previous_accumulative_consumption only works for the past 10 days, and there is also the problem of the ‘date’.
Exhibit A: - Octopus Energy Integration, electric (import) previous_accumulative_consumption, in kWh.
Since I have a Solar PV system, which is currently doing its job very well, my import has been just the system balancing jitter at around 0.5 kWh per day, otherwise this is what I believe you are after. Note that, for the 7th June, the figure changed at 13:19 in the afternoon. This is quite normal, since the value in this sensor is the accumulated (total) consumption (not cost) value for the day, and it comes from the smart meter to Octopus during the night, arriving in my account sometime between 09:00 and 20:00 on the day after. On a good day, I get yesterdays readings by 09:00, but on a bad day not until early evening. Gas is another matter, and not getting a reading at all for two or three days in a row can happen.
Therefore, the figure 0.593 (0.6 kWh) needs to be captured early evening for the 7th June, and plotted on a graph for the 6th June.
Home Assistant can do this, if you are happy to work with SQL calls to the database and process an array of data. For myself, I use Node-RED. So much easier.
I modified something else I already had so as to generate this:-
This is correct, in that the value captured during the evening of the 7th is actually plotted against the 6th. Thank you for asking the question, now I have another nice graph I wanted to add to my collection. It has been a very sunny spring, almost entirely ‘off-grid’ since the start of April…
If you want the code to do this, you need Node-RED with the additional node-red-node-sqlite node set. The following code will
There is other stuff in the code to build a year table by calendar month, and to build a billing table by Octopus billing date.
[{"id":"c5569d9c9b38ada0","type":"group","z":"2dd6b0b4a5f86125","name":"Consumption day-graph (for testing only)","style":{"label":true},"nodes":["c523a00720fbfd24","2b1273152bdb234e","f2a7abb264333158","c8ca6fb16faa1ead","03bdc0c644b5d3b2","92607112b89f4b45","343ae2b4d83cb0f5","1bec2fbf0ac54bc1","05a1494f02f9f815","36094bab72c774ae"],"x":34,"y":5819,"w":1072,"h":202},{"id":"c523a00720fbfd24","type":"sqlite","z":"2dd6b0b4a5f86125","g":"c5569d9c9b38ada0","mydb":"56ce7a19f8e148e7","sqlquery":"prepared","sql":"SELECT statistic_id, id FROM \"statistics_meta\"","name":"Long Term stats (name & id)","x":420,"y":5860,"wires":[["2b1273152bdb234e"]]},{"id":"2b1273152bdb234e","type":"change","z":"2dd6b0b4a5f86125","g":"c5569d9c9b38ada0","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_consumption","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":730,"y":5860,"wires":[["f2a7abb264333158"]]},{"id":"f2a7abb264333158","type":"debug","z":"2dd6b0b4a5f86125","g":"c5569d9c9b38ada0","name":"debug 6","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":990,"y":5860,"wires":[]},{"id":"c8ca6fb16faa1ead","type":"inject","z":"2dd6b0b4a5f86125","g":"c5569d9c9b38ada0","name":"Get long-term stats","props":[{"p":"params","v":"{\"$id1\":117,\"$days\":400}","vt":"json"},{"p":"billday","v":"24","vt":"num"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":170,"y":5940,"wires":[["03bdc0c644b5d3b2"]]},{"id":"03bdc0c644b5d3b2","type":"sqlite","z":"2dd6b0b4a5f86125","g":"c5569d9c9b38ada0","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) 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":400,"y":5940,"wires":[["92607112b89f4b45","1bec2fbf0ac54bc1"]]},{"id":"92607112b89f4b45","type":"change","z":"2dd6b0b4a5f86125","g":"c5569d9c9b38ada0","name":"Analyse","rules":[{"t":"set","p":"payload","pt":"msg","to":"(\t/* June 25 - mods to include a day table, backed to yesterday, for day-consumption reporting */\t\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, also midday timestamp for yesterday */\t $base:=payload~>|$|(\t $x:=$split(udate,\"-\");\t {\"month\": $number($x[1]),\t \"day\": $number($x[2]),\t \"year\": $number($x[0]),\t \"timestamp\": (udate & \"T12:00:00\"~>$toMillis())-86400000 })|;\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 \"dtable\": $base,\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 \"kWh\": $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\t)","tot":"jsonata"},{"t":"set","p":"payload","pt":"msg","to":"(\t $l:=$count(payload.import.dtable);\t payload.import.dtable[[$l-90..$l]];\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":640,"y":5940,"wires":[["343ae2b4d83cb0f5","36094bab72c774ae"]]},{"id":"343ae2b4d83cb0f5","type":"debug","z":"2dd6b0b4a5f86125","g":"c5569d9c9b38ada0","name":"debug 7","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":720,"y":5980,"wires":[]},{"id":"1bec2fbf0ac54bc1","type":"debug","z":"2dd6b0b4a5f86125","g":"c5569d9c9b38ada0","name":"debug 8","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":480,"y":5980,"wires":[]},{"id":"05a1494f02f9f815","type":"inject","z":"2dd6b0b4a5f86125","g":"c5569d9c9b38ada0","name":"Find id we want","props":[{"p":"params","v":"{}","vt":"json"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":180,"y":5860,"wires":[["c523a00720fbfd24"]]},{"id":"36094bab72c774ae","type":"ha-sensor","z":"2dd6b0b4a5f86125","g":"c5569d9c9b38ada0","name":"Oct Import Elec Daily Use Table","entityConfig":"1feb8331337d9514","version":0,"state":"payload[-1].state","stateType":"jsonata","attributes":[{"property":"table","value":"payload","valueType":"msg"}],"inputOverride":"allow","outputProperties":[],"x":950,"y":5940,"wires":[[]],"server":""},{"id":"56ce7a19f8e148e7","type":"sqlitedb","db":"/homeassistant/home-assistant_v2.db","mode":"RO"},{"id":"1feb8331337d9514","type":"ha-entity-config","server":"","deviceConfig":"","name":"SC Oct ImE Daily","version":6,"entityType":"sensor","haConfig":[{"property":"name","value":"Octopus Import Electricity Daily Consumption"},{"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":false,"debugEnabled":false}]
For this exercise I have curtailed the 400 output array to just the last 90 days, and set a usual Apex chart with data generator to pull from the table and plot the last three months.
type: custom:apexcharts-card
header:
show: true
title: Octopus Electricity Import (kWh) - Daily 3 months
show:
last_updated: true
graph_span: 3 months
span:
start: day
offset: "-3month"
series:
- entity: sensor.octopus_import_electricity_daily_consumption
data_generator: |
return entity.attributes.table.map((reading) => {
return [new Date(reading.timestamp).getTime(), reading.state];
});
type: column
color: Red
unit: kWh
I will leave you to experiment and provide a better version of my code in due course.
NOTE: I suggest leaving the sqlite node setting as ‘prepared statement’ and the configuration server to Read Only to prevent any accidental writes to the HA database!
Thanks a ton for the elaborated answer. It is very insightful. I appreciate it a lot!
Yeah, I was very satisfied with the charts generated from Octopus Energy integration with your inspiring solution until the Octopus Energy Home Mini went bonkers and no fed for days. Coincidently the metal ran the Home Assistant container ran into boot-up loops due to the boot SSD gave up, probably too much writes had been done to it. I migrated the Octopus Energy integration to another Home Assistant container running on a NAS. In the process I reckon there should be a better way to represent the data. Hence I rewrote all those charts and corresponding scripts, entities, automations and so on.
Back to the problem I have. My goal is far less ambitious than you thought. I just want to have a way to plot a chart with the timestamps as the value of attribute last_reset of various of *_accumulative_cost entities. I had no intention to retrieve data more than what is already in the database of Home Assistant.
I have created a SQL entity sensor.octopus_energy_electricity_daily_accumulative_cost with the following SQL:
SELECT
max(CAST(s.state AS REAL)) AS state,
SUBSTR(
a.shared_attrs,
INSTR(
a.shared_attrs,
'"last_reset":"'
)
+ 14,
INSTR(
a.shared_attrs,
'","mprn"'
)
- 14
- INSTR(
a.shared_attrs,
'"last_reset":"'
)
) AS ts
FROM
states AS s
INNER JOIN
state_attributes AS a
ON
s.attributes_id = a.attributes_id
WHERE
s.metadata_id = (
SELECT
metadata_id
FROM
states_meta
WHERE
entity_id = 'sensor.octopus_energy_electricity_[acc]_[mpan]_current_accumulative_cost'
)
AND
a.shared_attrs LIKE '%"last_reset":%'
GROUP BY
date(s.last_changed_ts, 'unixepoch')
ORDER BY s.last_changed_ts DESC
LIMIT 1
;
The SQL outputs the following from sqlite3 if I omitted the LIMIT 1 part from the SQL:
state|ts
0.36|2025-06-13T23:00:00+00:00
0.44|2025-06-12T23:00:00+00:00
0.42|2025-06-12T23:00:00+00:00
0.41|2025-06-10T23:00:00+00:00
1.45|2025-06-09T23:00:00+00:00
0.48|2025-06-08T23:00:00+00:00
0.35|2025-06-07T23:00:00+00:00
0.35|2025-06-06T23:00:00+00:00
0.45|2025-06-05T23:00:00+00:00
0.33|2025-06-04T23:00:00+00:00
0.37|2025-06-03T23:00:00+00:00
0.5|2025-06-02T23:00:00+00:00
0.4|2025-06-01T23:00:00+00:00
0.48|2025-05-31T23:00:00+00:00
0.33|2025-05-31T23:00:00+00:00
1.45|2025-06-09T23:00:00+00:00
So how do I plot a chart with attribute ts as the timestamp instead of the last_changed_ts?
The apex-charts card just requires an array of array-pairs of [timestamp (unix ms), value]. Usually the data generator is used to produce this array.
As long as you have your array of data values and times, in an entity attribute, all you need to do is map over this (either using the map function or by using a for-loop) picking out the value, and the timestamp of your choice. Both the plot-value and the plot-timestamp can be computed from any available value in any way required.
On its own, SQL can generate a table, but the challenge is getting this into a table in an entity attribute, from which the apex-graph chart can work. If that is your question “how do I get the value of an SQL query into an entity” then, for myself, I use Node-RED. Easy to perform the computation, and importantly NR permits creating entity sensors, and uploading whatever you want into the entity attribute. HA has an SQL sensor integration, but this only permits one value from a table-return to go into the entity state value. HA is not really setup to process arrays and tables of data.
After re-reading the data_generator section of the ApexCharts Card documentation, I realized that the data_generator can only process from the latest state of the entity. Hence it means I have to create an entity that carries all the historical data every time when the state is updated in order to get it working. 
Now I am exploring how to accomplish a month long daily cost chart with Node-RED. Thanks for introducing it to me.
Let me report back when I have achieve what I have in my mind.