Backfilling old data in EmonCMS

I’ve recently installed the newest EmonCMS image on an RPi3. Previously I’ve collected power generation and usage data in a Zabbix monitoring system’s MySQL database to allow simple monitoring of my system. Now I want to migrate that data to EmonCMS. I’d like to ask for some advice on this. My current plan is to use the PHPfina API to backfill my ~2 months worth of power data and then generate the relevant energy metrics using usefulscripts/power_to_kwh.php at master · emoncms/usefulscripts · GitHub. Would this be a way to do this? Has anyone done something similar?

Alright, I decided I only need to set a starting value for energy to correct for the days when my OEM system wasn’t operational yet. How would one do that?

BTW, I received no answers from anyone here for 3-4 days, is this the proper forum for EmonCMS support?

Yes it is the right forum, just not many users to help with such a specific request.

I have in the past done something like this and found it is possible, I am not an expert as I have not done this enough times to refine the process but in loose terms you will first need to create the feeds you want without initializing them.

You can do this via the feeds api if you are familiar with it or as I choose to do, you can create an input by simply sending one (or more) of your historical data frames, I usually test my “uploading” first to confirm the data is appearing in emoncms ok and then stop it (obviously you must use an api that allows you to provide the timestamp and doesn’t timestamp on arrival). Choose a send frame format that delivers the data to suit your ongoing (current?) data posting.

It is important no data is posted to those inputs created whilst doing the next part.

Using the inputs created, set up your processing which includes creating your feeds as usual. Make sure if using phpfina that you choose an interval that matches your timestamped data. When you do this the mySQL tables are updated with the details of the feeds, but the feeds themselves do not exist yet until you post to them.

When you are done you can send your data starting from the oldest data frame first. The first frame is the most important one as it triggers the creation of the data feed files and sets the “point zero” for the time base in phpfina feeds. (I seem to recall this can be up to 5yrs old).

Once you have done that, you can repost any interim data collected in emoncms since switching to that same input which will hopefully bring you up to date, it is then a simple case of changing your existing (live) inputs to use the feeds containing the historical data and delete the now redundant extra inputs. Or you can change the node id of your live data at the source end so that the live data is posting to those same newly created inputs and delete the original.

I hope that makes sense, there are probably many variations on the method used, the key part is to get all you new feeds and processing set up before posting the oldest data first to create “old” feeds and then get the current data stream to append to those feeds moving forward.

If you set up the input processing before uploading the historic data you should not need to use the power_to_kwh.php script, that is aimed at existing feeds.

EDIT - Just to clarify, you cannot “backfill” as you cannot post data older than the creation time of the feed, but you can create new feeds with an older/earlier start time.

EDIT2 - You can also reuse your existing inputs and processing if you choose to stop the live data and simply swap out all the existing feeds in that inputs processing for new (un-initialised) feeds then post oldest data first, before resuming live posting.

This is very much simplified when using original emonhub ( the sd card uses an “emonPi variant”) due to buffering, when I do this I can just pause sending data and take my time to get all historic and interim data in place before resuming live data to the same inputs, this is not possible with the “emonPi” variant, somewhere you are likely to have a bit of a hole in your data.

1 Like

@pb66 thanks for the extensive write-up. I realised in the meantime that I can’t “backfill” everything but I have a running sum of my consumption and generation. So I devised a simple plan:

  1. Stop EmonHub
  2. Clear energy feeds
  3. For each energy feed: use power_to_kwh.php to calculate energy values with a starting value set inside the script by hand
  4. Start emonHub and observe what I just did on EmonCMS

I can calculate the starting values using my inverter’s data (easiliy accessible online) and the smart meter (eyeballing values). Using this method I’ll lose some data packets but I can live with it. Scripted properly it shouldn’t even take more than 10 seconds since I only have a few days of data stored in EmonCMS.

What do you think, could this be a feasible method?

I’m no longer sure I know what you are trying to achieve, I assume you have correctly created energy feeds based on your other thread about the apps not populating, so if you are not wanting to “back fill” now as originally requested, what are to aiming to achieve by using the power_to_kwh.php script?

Are you just simply trying to align your running totals with your meter readings?

OK, sorry for the confusion. First, please see my 2nd post here: Backfilling old data in EmonCMS - #2 by kobuki

I have set aside backfilling old instantaneous power values, they are not as important now. So, what I think is necessary for the correct operation of my energy monitoring is for it to show energy values corresponding to the actual, real values on my meter and inverter. Since EmonCMS is not running from the moment when my solar system was installed, I would need to set an initial energy sum value on all feeds. For that, I’ve outlined a method that I think should work. (Noting here that EmonCMS really should provide an easy way for this.)

Please tell me if further clarification is needed.

I agree there could be an easier route, but it is just a case of “log to feed” the value you want and then accumulate from there, although these monitors are pretty accurate it may not track the meter readings precisely so may need correcting now and then, again that could be done just by using a “log to feed” you wouldn’t rerun the “power_to_kwh.php” script to correct the end value.

I’m not sure I would have bothered with the script if I only had a few days data, but there is no reason it shouldn’t work.

Well, yes, I’m aware I’ll need to make additional corrections later, that’s not an issue. But actually the solar panels were installed on 1st July so I have a large 2-month gap in my energy metrics. I’d like to correct that to be able to follow my real yearly consumption/generation figures and expected cost (at start, 1-2% accuracy is good for me, and I think it can be reasonably achieved).

Hi,

Now I understand that i can’t insert older feeds that the timestamp of the first feed that created that feed.

Is there no solution to change this creation timestamp? Is it possible with an sql statement? Or insert the date with sql statements?

I realy want to insert my historical data but now i can’t anymore.

Thanks in advance

You can fill your old data using a custom script and the PHPFINA API from OEM. I was to do that but I realised I only need a current kWh accumulation value. For that see my previous hint about using power_to_kwh.php (at /home/pi/usefulscripts/process/ on your EmonPi). Well, I haven’t really gotten around doing that yet but will obviously need to since my current kWh values in EmonPi are lagging behind real usage.

I wonder power_to_kwh.php is the solution for my purpose…

I have a table like (but than much longer):

Every value is Kwh per day.

I wanted to insert each line with:

http://192.168.0.251/emoncms/feed/insert.json?id=4&time=1480588800&csv=0.5

So changing the creation value of the feed would be the ultimate solution for me I think.

Is that possible or @kobuki , is your solution usable for my situation without losing my current new data?

Thanks…

I think the question would better be addressed to the OEM folks, but AFAIK you cannot insert old data into the FINA DB. So in effect you’d need to re-generate all of it, including your old and already stored values.

The start date of the feed is the crucial element, but you cannot just change it. The timestamp for each of the datapoints in the feed is derived from it’s position in the file and the start date. For example if you were to edit the startdate to be 1 week earlier, the data you posted yesterday would now be dated 8 days ago and there would be no data for the week ending yesterday, plus any “backfilling” you do would overwrite your existing data. Effectively moving the startdate back, moves all your current data back, leaving a hole at the end not at the beginning.

One way to do it is to create a new feed with the same interval and an appropriate start date, post all your “backfilling” data from your table using a small script and then download csv or create a table from the feed currently in service and upload that data to the same new feed, the same way as for the old data and then swap the feeds over.

Ok, thanks, thats clear.

I’ll try to do it as you explaned…

Ok, still got stuck.

This is how you can make a feed:
http://192.168.0.251/emoncms/feed/create.json?tag=Test&name=Power&datatype=1&engine=5&options={“interval”:10}

But I can’t find how to make a new feed with a certain start date.

Any suggestions?

Thanks…

According to this thread in the old forum, you should be able to use timestamps in your URL:

emoncms/api/post.php?apikey=yourapikey&time=unixtimestamp&json={power:200}

I think you can use the very first post to set the starting date and then post the individual values.

Edit: see reference here.

The feed is actually initialized with the first datapoint not when you “create the feed” via the emoncms api or webpages.

If you specify the type and interval when tou create the feed, that is the feed defined, and then when you post your first data, the timestamp of that first data sefines the starttime, so you must use your earliest data first to avoid being back in the same situation where you cannot post data earlier than the starttime.

Then it seems my assumption was right about the first data value setting the starting timestamp of the feed. Good to know in any case.

@Fiets: may I ask what the source is for your historical data?

Hi,

I think the solution of Kobuki doesn’t work in my case. I can’t insert data in Watt.

My source of historical data is from the invertercompany Growatt. So I have KwH per day (the only thing I can export from there). So for every day I have just one record.
So I thougt just to insert feed data in the feed “Zon Vandaag KwH” (Sun today KwH)

So still no solution (or I didn’t understand the suggestions made)…

Hmm, if you have only daily kWh data, you still can import your stuff. You just need to convert your daily kWh energy values to instantaneous power values (W) for every sample. You would need to post a constant Watt value for all samples of a day. The daily constant is then:

Wsample = (daily kWh) / 24h / 360

Assuming the standard 10 sec sample intervals (360 = 3600s / 10s). You obviously won’t be able to see intra-day instantaneous power curves.