I did not find the solution what I was looking for so far, but here we are at Christmas break with a little free time. I had a Shelly 3EM power meter, monitoring my energy import. I had 1 phase system, used 2 channels for measuring my ground floor and top floor separately.
In the summer we had the energy meter replaced with the newer one having a P1 port. I went for a homewizard P1 which is a nice improvement: I see the actual numbers I am charged for.
The Shelly was operational for 2 years and I wanted to keep the old data + also repurporse the Shelly to meter individual devices. I don’t know any way this is currently achievable on the GUI. You need to keep the power sensor in the Grid consumption otherwise historical data won’t show.
I cannot keep the Shelly here anymore, otherwise it would be counted double once I repurpose it.
My thought process was:
- Move historic data from Shelly to P1
- Remove Shelly from Grid consumption, add it as individual device
If you only plan the switch and have not yet installed the new device, you can try this method. I already had couple months of data on P1 before getting here, it was no longer an option for me.
Step 1 can be achieved via SQL. My recorder runs on MariaDB and I have phpMyAdmin installed to run SQL commands.
The statistics data is stored in the statistics table. There is a helper table called statistics_meta that links these tables together. I used the latter to find the ids needed to perform the changes.
My shelly sensors were with ID 105 and 111, while my new P1 sensor is on 429.
First and foremost create a backup of your choosing at this point.
Let’s also create a backup of the statistics table before anything happens:
CREATE TABLE statbackup AS SELECT * FROM statistics;
Let’s create a working table that we will be messing up. We don’t need everything, just what is related to our entities.
CREATE TABLE stattest AS
SELECT *
FROM statistics
WHERE metadata_id IN (105, 111, 429);
As the Shelly and P1 were not running in parallel I don’t have to solve that problem. The P1 has 1 sensor for the 2 Shelly. I combined the state and sum values by the timestamp start_ts into a new row, with the P1 meter’s ID (429). The other columns were empty, so I did not use them.
INSERT INTO stattest (metadata_id, start_ts, state, `sum`, created_ts)
SELECT
429 AS metadata_id,
a.start_ts,
(a.state + b.state) AS state,
(a.sum + b.sum) AS `sum`,
a.created_ts
FROM
stattest a
JOIN
stattest b
ON
a.start_ts = b.start_ts
AND a.metadata_id = 105
AND b.metadata_id = 111
The command takes some time to run, depending on your computing power and DB size.
We have a new row for each pair of data summarized.
Note: if you have 1 to 1 sensor instead of the 2 to 1 I had, you run this command. (This is a different use case, don’t run it with the previous command!)
UPDATE stattest
SET metadata_id = 429
WHERE metadata_id = 105;
Optional step: remove the legacy data as it won’t be used anymore:
DELETE FROM stattest
WHERE metadata_id IN (105, 111);
Once you confirm the results are of expected, you run the command on the statistics table instead of stattest. Next head over to the Energy dashboard to check the results.
Before
After
You can remove the old sensors from the grid configuration at this point.
You can remove the test and backup tables once you confirmed everything is fine.
DROP TABLE stattest;
DROP TABLE statbackup;
Almost everything looked fine, except on the day the P1 meter started logging the statistics the Energy dashboard showed a huge negative value. Why? Because the P1 port started from 0 while my Shelly data was already at somewhere much higher. The energy dashboard will do a subtraction.
To fix it, go to Developer tools > Statistics > Find the energy entity > Click adjust sum > go to the date with the big number > fix the value, or if not displayed right away, click Outliers. Once you set the bad value to 0, refresh the Energy dashboard.