PHP MySQL Energy Script

When the firm Giv Energy announced it was going into administration - I decided it was time to figure out how to keep a backed up copy of all the data that was stored within it's cloud infrastructure.  I wanted to not only keep hold of my data beyond the life of any associated company - but wanted flexibility to search and analyse my data in a way that is only possible with self hosting.

To achieve this, I created a PHP based script - that interfaces over API calls - to not only GivEnergy - but into the Octopus Energy system as well.  

The goal was to achieve a solution that extracted the maximum permissible granularity of data - in most cases - the half hourly slots we see from our smart meters.

The result can be found in this public GitHub repo here.

 

Overview of how to go about installing the solution.

  1. Get yourself a server / desktop / laptop / anything that can run MySQL and PHP
  2. Install MYSQL and PHP - I've developed this against PHP 8.4, I used MySQL Workbench for a Windows 11 friendly UX
  3. Get hold of the code from here: https://github.com/neway/home-energy-data
  4. Configure yourself a givenergy.ini file by using the sample
  5. Go and get all of your API keys from Giv & Octopus - you'll also need meter numbers etc. - add these to your ini.
  6. Create your MySQL database - adding a user for access - with the relevant permissions - add these to the givenergy.ini file
  7. Kick off an initial run: php givenergy_import.php --config=/path/to/givenergy.ini --date=2023-07-27
  8. If looking good - continue with the --auto-catchup flag - this will continue from where left off
  9. If you have are able - setup a cron job - so daily the backup gets added to...
  10. Run a few SQL reports to get a feel for your data...

 

SELECT sum(sum_grid_import_kwh),
DATE_FORMAT(period_date, '%Y-%m') AS yyyy_mm,
count(*)
FROM solar.givenergy_energy_flows
where  grouping_type = 1
GROUP BY DATE_FORMAT(period_date, '%Y-%m')

SELECT
 fuel_type,
 DATE_FORMAT(summary_date, '%Y-%m') AS yyyy_mm,
 SUM(total_consumption_kwh) AS total_kwh,
count(*)
FROM solar.octopus_daily_summary
GROUP BY fuel_type, DATE_FORMAT(summary_date, '%Y-%m')
ORDER BY fuel_type, yyyy_mm;