I just changed the sensor for the gas consumption from m³ to kWh. Because the invoice is with kWh. And it gets comparable, because everone is talking about kWh.
But how can I transfer the data from the old m³ to the new kWh sensor. Is the conversion itself from the values should be easy. Because I measure still in m³.
But I dont know how to converse the values in the db and transfer it to the new sensor.
Any ideas?
With old values, I guess you mean the statistics you gathered over time.
If you know the calc on to convert them, you can just do this in the db itself with a small sql statement
Convert m3 To kWh (Gas Cubic Meter To kWh Calculator) - LearnMetrics
What db you use and do you know how to SQL it?
Yes you are right. I mean the statistics. The calculation is not the problem.
I use mariaDB. I already thought to do this directly in the DB, but have no idea how to do it.
I remember that there is a tool called „phpmyadmin“ where you can edit DBs. Is this working for mariaDB? I installed mariaDB directly in Homeassistant.
Indeed… I use phpmyadmin for that…then you need to find the id of the sensor in statistics, do the SQL…done, just make a backup if not 100% sure 
I found this Migrate energy statistics from one entity to another - #7 by BrentV
UPDATE or REPLACE statistics
SET metadata_id = (SELECT id --Set the new entity name
FROM statistics_meta
WHERE statistic_id = 'sensor.gaszaehler_kwh')
WHERE metadata_id = (SELECT id -- Old entity name
FROM statistics_meta
WHERE statistic_id = 'sensor.gaszaehler')
But it throws 3 errors.
or REPLACE
or REPLACE
statistics
I am a total noob in case of sql. Would you be so kind an help me?
So, first of all I would like to know if you want to copy data or replace/update existing data. My guess is that you donot need the copy as you would like to continue with that one, just with different figures, or?
Then, you need to find out if all records have to change to the new values
So, first of all check the response from this one, assuming your current sensor is “sensor.gaszaehler”
SELECT * FROM `statistics` WHERE metadata_id = (select id from statistics_meta where statistic_id = 'sensor.gaszaehler') order by created DESC
This will list the values with newest on top…
Option 1: If you see these are indeed the records and that you want to update all then …
update statistics set state = state * YOURVALUE, sum = sum * YOURVALUE where metadata_id = (select id from statistics_meta where statistic_id = 'sensor.gaszaehler')
Option 2: If you donot want to update all then you might need to add a condition on date or (easier) the ID in the statistics table…e.g. where (…) and ID < 123456… the ID’s are visible in the first select statement
all in all I strongly recommend to make a copy of your DB first… I can help but am human too and not willing to be liable
Backup is already made
I want to copy them, because i want to log the m³ values in the background. I have to report the m³ to my supplier, which then calculate my invoice with kWh.
So i want to copy it to sensor.gaszaehler_kwh and then recalculate from m³ to kwh.
Your first input is working, I can see the values. So before I recalculate the values I have to copy them to the new sensor.
How can I copy them?
So you want to copy to “sensor.gaszaehler_kwh” and you already have that sensor active?
If that is the case then…
insert into statistics (created, start, state, sum, metadata_id) select created, start, state, sum, (select id from statistics_meta where statistic_id = 'sensor.gaszaehler_kwh') from statistics where metadata_id = (select id from statistics_meta where statistic_id = 'sensor.gaszaehler') order by created asc
I am not 100% sure if this works (select id from statistics_meta where statistic_id = ‘sensor.gaszaehler_kwh’)…else you need to hardcode it with the id of sensor.gaszaehler_kwh
EDIT: it is important to have the statistics created in the right order so asc may need to be desc… I am not sure how mariadb works when sorting during insert…meaning that the newest ID in statistics should also be the newest reading of the values…else it goes wrong…but you can always delete and restart
And… probably you need to remove the statistics alread made by gaszaehler_kwh … if any
I updated the query again… so do check
Wonderful, everything worked.
I first deleted all old entries from sensor.gaszaehler_kwh manually.
Then copied the old values from sensor.gaszaehler to sensor.gaszaehler_kwh
insert into statistics (created, start, state, sum, metadata_id) select created, start, state, sum, (select id from statistics_meta where statistic_id = 'sensor.gaszaehler_kwh') from statistics where metadata_id = (select id from statistics_meta where statistic_id = 'sensor.gaszaehler') order by created asc
asc was the right sorting.
and then recalculated the new values from sensor.gaszaehler_kwh.
update statistics set state = state * 10.209547, sum = sum * 10.209547 where metadata_id = (select id from statistics_meta where statistic_id = 'sensor.gaszaehler_kwh')
All data from the past is displayed correct, wonderful! Thank you very much.
For everyone with the same problem, to calculate from m³ to kWh I used the formula my supplier is also using.
m³ * condition number * calorific value so in my case
m³ * 0,9043 * 11,290
Great… if possible, please mark one of my posts for ‘solution’ somewhere next to the ; heart;; icon…that may help others to search more efficient…note that this option is not always there
I tried to, but none of your posts can be marked as solution 
NP…or here in France: tant pis …most important… you moved on
Very important!
It has to be done on statistics_short_term also, otherwise the next sum will be wrong.
