Backfilling old data in EmonCMS

The discussion so far has been predominantly about phpfina (fixed interval) feeds rather than phptimeseries (variable interval). your “Zon Vandaag KwH” feed is phptimeseries.

Essentially the same applies, you cannot insert data prior to the feeds start time and with phptimeseries you cannot even insert new datapoints after the feed starttime if they are before the last post time because the datapoints are timestamp and value pairs, written to file in the order they arrive and cannot be moved without rewriting the whole file from that inserted timestamp to the end of the file to make room. So in short you can only append new data after the last datapoint or edit an existing datapoint.

But the solution is also similar, you can create a new “daily” phptimeseries variable interval feed without initiating it and then use a feed api to write your data to it. using something like

https://myserver/emoncms/feed/create.json?name="Zon Vandaag KwH"&datatype=2&engine=2

you should get a response like

{"success":true,"feedid":1234,"result":true}

where “1234” is the feedid allocated by emoncms, you will need that feedid to post your data, then set up a script to loop through all your old data (presorted into date order!!) and post using this url, changing the time and value each time.

https://myserver/emoncms/feed/insert.json?id=1234&time=1414579200&value=0.4&apikey=abc123

You will need to add your write apikey for the script to work as it will not matter if you are logged in via a browser.

It is essential the data you post is in chronological order, there is no inserting or backfilling, you will need to start again if you miss one or get the order wrong.

Once you have the old data in place you can use a feed api to export the current feed data and re-post it in exactly the same way as you did your old data, to the same feed.

You can then swap them over in your input processing and dashboards etc and delete the old feed if you are happy with it. You may see a slight error in the value for the day you make the switch, but it should be minimal (depending on time of day and time taken) and that days value can be edited if needed.

Yes it was.

The instantaneous values won’t mean anything when viewed directly and they cannot be used to graph accumulated values, however it might be possible to use post-processing to create a kWh accumulating total feed that can be used for graphing daily totals from.

Right, there are 2 things to consider. One is the instantaneous historical graph and another is the accumulated energy graph. The former can be backfilled using my previously mentioned method. For the latter it’s possible to use the power_to_kwh.php script.

Ok pb66, I will try that.

But i saw I could also feed to SQL. That way I can easilly inset historical data. Maybe only voor the daily data.

I understand the data is in memory for a certain time. But how big can the los be? Will the data in memory flushed to SQL when I give the shutdown command?
What is the disadvantage of feding to SQL?

And why not doing both so you can report from SQL and have the other way to be sure?

Thanks…

To the best of my knowledge EmonCMS doesn’t use MySQL for historical data at all (older releases used it IIRC). It would be very easy to add old data if it did. You can do it like @pb66 described but I think my suggestion is a lot simpler (for me, at least). Anyway, please report back with your experience if possible.

I just added feeds, the last tree feeds are copies of the tree feeds above but than to SQL.

This way I make a life copy to SQL.

Feed data is not in the SQL DB, only feed config… So yes, you can copy and swap configs but not data.

You are using mySQL IN MEMORY for those feeds, there are not persisted and data will be lost on a restart or flush etc.

This is not strictly true.

You can indeed use full mySQL tables persisted to disk if you enable the mySQL engine in the settings.php. It is not recommended that you use the mySQL engine with an SD card as it is write intensive and can increase the wear rate to the SD card and possibly lead to an earlier failure. But maybe you would find it easier to use the mySQL engine short term to complie and collate you data from different sources and then convert mySQL to another feed type once merged and then disable mySQL again?

You can configure the CMS in many ways including mysql, sure, and then some operations become possible, but that’s not the preconfigured and recommended way. Though, data collection in mysql and then migrating to PHPFINA could be a good idea and could simplify things (subject to personal skill level with DBs). I’m running my EmonSD entirely from an Intel SSD (no SD card involved at all) and for such setups even a fully mysql based solution would be viable but I’ll stay with the defaults.

@Fiets have you been able to fill your db using one of the mentioned methods? I’m curious about your own experience.