Copy data from InfluxDB to MySQL for monthly charts and Time of Use pricing

[youzer-name]

Edit: For step-by-step instructions to set up MySQL, skip to the second post in this discussion. Some of the details in this post are out of date

---

While trying to create a chart that would show metrics (solar production, for example) by month of the year, I ran into a number of limitations of InfluxDB and Grafana that made it impossible to create such a chart. I thought that I would have better luck if I could get my data into MySQL or another SQL-type database.

Specifically, there is no 'datepart(datefield)' type function in InfluxQL, so it isn't possible to get just the month part of a date to use in a chart. The downsampled data in kwh has a month field, but that field is alpha (Jan, Feb) which results in the months being sorted alphabetically on the chart rather than in order from January to December.

I tried using the Flux query language instead of the InfluxQL language, as it is supposed to support operations similar to 'datepart', but didn't have any luck, although someone who is fluent in Flux might be able to achieve the same result without resorting to copying the data.

In my case I have the Powerwall Dashboard stack running under Docker on a Raspberry Pi 4. The first thing I needed was a MySQL server. I initially thought about installing MariaDB on the 32-bit ARM OS on the RPi, but for me it was easier to set up the MySQL on a Windows box under Docker desktop. I'm not sure if using MariaDB on a RPi would cause any issues with this solution. Once it was running I set up the root user password and created a database schema called powerwall_mysql.

[ Edit: I later set this up on the RPi Docker host as noted below. It just required a small change to the query) ]

The next step was to get the kwh data from InfluxDB to MySQL. The kwh data is downsampled to hourly totals for each metric, and I didn't see a need to have more detailed data than that. You could also use the daily downsampled data. I ended up pulling both tables and the script below will export both.

I found a YouTube video that showed a Python script to copy InfluxDB data to MySQL. With a little bit of modification I was able to make that script do what I needed. I had to install several Python modules including sqlalchemy, pymysql, pandas, and influxdb to get the script to run.

There are two scripts below. One is for the initial data pull. It will create the necessary tables in MySQL, over-writing any tables that already exist with the same name.

Scripts

[ Edit: The script has a line that can be uncommented to create a local_datetime column. I recommend doing that as I later ran into a situation where the local time was needed, see discussion on Time of Use rates https://github.com//issues/113 ]

## 1. Importing Modules

from sqlalchemy import create_engine
from influxdb import DataFrameClient

   
## 2. Create a client to Connect to Influx DB

client = DataFrameClient(host='localhost', port=8086)
client.switch_database('powerwall')

## 3. Run the hourly query and store results in a dataframe

results = client.query('select * from kwh.http order by time')
df = results['http']

## 4.. Copy datetime to 'datetime' column - by default it is in the 'index' column

df = df.reset_index()
df['datetime'] = df['index']
## Uncomment the next line to create local TZ timestamp if needed, using your TZ
# df['datetime_local'] = df['index'].dt.tz_convert('America/New_York')

## drop the original 'index' column
df = df.drop(columns = ['index'])

## 5. create mySQL Connection

engine = create_engine("mysql+pymysql://{user}:{pw}@192.168.0.xx/{db}".format(user="root", pw="password", db="powerwall_mysql"))

## 6. Export data to MySQL

df.to_sql('kwh', con=engine, if_exists = 'replace', chunksize =1000, index=False)

## 7. Repeat above for the daily data. Do not pull today's data - it is only a partial sum
results = client.query('select * from daily.http  where time < now() -1d order by time')
df = results['http']
df = df.reset_index()
df['datetime'] = df['index']
df = df.drop(columns = ['index'])
engine = create_engine("mysql+pymysql://{user}:{pw}@192.168.0.xx/{db}".format(user="root", pw="password", db="powerwall_mysql"))
df.to_sql('daily', con=engine, if_exists = 'replace', chunksize =1000, index=False)

## You must add keys/indexes to MySQL tables after the initial import, before running periodic updates

print("Program Completed")

Note: step 2 uses 'localhost' as I ran the script on the same RPi where the Powerwall InfluxDB resides.

Once I had the data in MySQL I created a new datasource in Grafana that connected to the MySQL server. Using that datasource I created a new bar chart with this query:

SELECT any_value(datetime) as 'time', month(datetime) as 'metric',  sum(solar)
FROM kwh
WHERE  $__timeFilter(datetime) 
group by month(datetime) 

The "any_value" operator was a new one for me. This was to work around the fact that I didn't want to group by the datetime, but MySQL usually requires each metric in the select statement to be included in the group by statement. This could also be solved by disabling the "only_full_group_by" setting in the MySQL configuration. There is some discussion of this here: https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html

[ Edit: I ended up moving the MySQL from a Windows Docker Desktop container to my Raspberry Pi (which is running ARM32 V7l), using https://github.com/yobasystems/alpine-mariadb. This didn't accept the "any_value" query. A simple workaround was to replace "any_value" with "min" (or "max" or any other aggregate function) so it now reads: SELECT min(datetime) as 'time', ]

I then created charts for 'Home Usage', 'From Powerwall', and 'From Grid', set the colors to match the existing daily (past month) charts, and set a soft max on each chart so they would all be on the same scale. I don't have a full year's data yet as I only got my Powerwall at the end of February, so the end result looks like this:

The queries can be set up with either the kwh or the daily data. If you use the daily data, it won't include the partial sums from, today, but since these are monthly charts, the one that includes today is always a partial month anyway, so either way probably works.

The last steps are to add unique keys/constraints to the MySQL tables so that duplicate data can't be created when updating the data, and to schedule the python script to run (via cron or task scheduler).

For the kwh table, a primary key needs to be created on the datetime column. For the daily table, a unique index is needed on the datetime and month fields. This is to deal with data that was logged prior to the bugfix in #80. If you don't have data from before that fix, you can just create a primary key on datetime.

In MySQL:

MySQL query

ALTER TABLE `powerwall_mysql`.`daily` 
ADD UNIQUE INDEX `no_duplicates` (`month`(3) ASC, `datetime` ASC);
;

and

ALTER TABLE `powerwall_mysql`.`kwh` 
CHANGE COLUMN `datetime` `datetime` TIMESTAMP NOT NULL ,
ADD PRIMARY KEY (`datetime`);
;

Finally, using cron or task scheduler, run this script to update the data periodically. I am running it every 5 minutes.

Python script

## 1. Importing Modules

from sqlalchemy import create_engine
from influxdb import DataFrameClient

# next section is needed when running incremental updates to handle duplicates
from sqlalchemy.dialects.mysql import insert
def insert_on_duplicate(table, conn, keys, data_iter):
    insert_stmt = insert(table.table).values(list(data_iter))
    on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(insert_stmt.inserted)
    conn.execute(on_duplicate_key_stmt)
    
## 2. Create a client to Connect to Influx DB

client = DataFrameClient(host='localhost', port=8086)
client.switch_database('powerwall')

## 3. Run the query and store results in a dataframe

results = client.query('select * from kwh.http where time > now() -2d order by time')
## (this pulls all data for the last 2 days. Modify as needed based on how often you run the script.

df = results['http']

## 4.. Copy datetime to 'datetime' column - by default it is in the 'index' column

df = df.reset_index()
df['datetime'] = df['index']
## Uncomment the next line to create local TZ timestamp if needed, using your TZ
# df['datetime_local'] = df['index'].dt.tz_convert('America/New_York')

## drop the original 'index' column
df = df.drop(columns = ['index'])

## 5. create mySQL Connection

engine = create_engine("mysql+pymysql://{user}:{pw}@192.168.0.xx/{db}".format(user="root", pw="password", db="powerwall_mysql"))

## 6. Export data to MySQL

## Using 'method=insert_on_duplicate' prevents errors if the script attempts to copy a duplicate record
df.to_sql('kwh', engine, if_exists = 'append', chunksize =1000, index=False, method=insert_on_duplicate)

## 7. Repeat above for the daily data. Do not pull today's data - it is only a partial sum
results2 = client.query('select * from daily.http  where time < now() -1d and time > now() -3d order by time')
df = results2['http']
df = df.reset_index()
df['datetime'] = df['index']
df = df.drop(columns = ['index'])
engine = create_engine("mysql+pymysql://{user}:{pw}@192.168.0.xx/{db}".format(user="root", pw="password", db="powerwall_mysql"))
df.to_sql('daily', con=engine, if_exists = 'append', chunksize =1000, index=False, method=insert_on_duplicate)

engine.dispose

print("Program Completed")

[youzer-name]

Based on the discussion in #113, I'm adding to this post to try to create a step-by-step set of instructions for adding MySQL to Powerwall Dashboard, including the monthly charts and time of use pricing.

Part 1: Install and configure MySQL

• I used a MariaDB Docker version that is compatible with my 32 bit ARM Raspberry Pi. You can install a MySQL-compatible database anywhere on your network that can be reached by your Grafana instance. I named my container "pimysql"

docker run --name pimysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=secretpassword -d yobasystems/alpine-mariadb:10.6.9-arm32v7

• Connect to the docker container and setup the DB. The syntax for docker exec may be different for a different database package

docker exec -it pimysql bin/sh
mysql -p

• Enter secretpassword that was used in the docker run command and then run these commands:

create database powerwall_mysql;
USE powerwall_mysql;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'secretpassword';

Create kwh table in MySQL:

``` CREATE TABLE `kwh` ( `from_grid` double DEFAULT NULL, `from_pw` double DEFAULT NULL, `home` double DEFAULT NULL, `month` text DEFAULT NULL, `solar` double DEFAULT NULL, `to_grid` double DEFAULT NULL, `to_pw` double DEFAULT NULL, `year` text DEFAULT NULL, `datetime` datetime NOT NULL, `datetime_local` timestamp NULL DEFAULT NULL, `cost_solar` decimal(15,10) DEFAULT NULL, `cost_grid` decimal(15,10) DEFAULT NULL, `credit_grid` decimal(15,10) DEFAULT NULL, PRIMARY KEY (`datetime`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ```

Part 2 - Copy data from InfluxDB to MySQL

• Ensure you have all the necessary packages installed in Python for the data replication script. On my system I had to add sqlalchemy, pymysql, pandas, and influxdb. If you are missing any packages you should get an error listing what you need to install

• Execute the initial python script to copy data from InfluxDB to MySQL.

  • Below is a modified version of the script from the first post. I removed the 'daily' table from this version and it only copies the kwh data. In my case the daily data had too many rows with duplicate datetime and month fields after running the historical data import tool.
  • You can add it back from the script in the first post if your InfluxDB data is cleaner and you want the daily table also.
  • It would also be easy to recreate the daily table entirely within MySQL rather than copying it.
  • This script also has the option for the datetime_local field uncommented. I found it was essential to have that field for TOU pricing.

• Set the port of your InfluxDB database in step 2 if it is not localhost:8086

• Change the time zone in step 4 from America/New_York to your time zone.

• Set the IP and password for your MySQL server in step 5

EDIT 2023-03-13: The original Python script automatically created the kwh table in MySQL. This caused issues with DST as the datetime field was being created as a 'timestamp' type and not a 'datetime' type. These instructions have been updated to manually create the table (above), and the scripts below have been updated so the initial load does not recreate the table.

Click to expand Python script

## 1. Importing Modules

from sqlalchemy import create_engine
from influxdb import DataFrameClient

## next section is needed when running incremental updates to handle duplicates
from sqlalchemy.dialects.mysql import insert
def insert_on_duplicate(table, conn, keys, data_iter):
    insert_stmt = insert(table.table).values(list(data_iter))
    on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(insert_stmt.inserted)
    conn.execute(on_duplicate_key_stmt)
    
## 2. Create a client to Connect to Influx DB

client = DataFrameClient(host='localhost', port=8086)
client.switch_database('powerwall')

## 3. Run the query and store results in a dataframe

results = client.query('select * from kwh.http order by time')
## (this pulls all data for the last 2 days. Modify as needed based on how often you run the script.

df = results['http']

## 4.. Copy datetime to 'datetime' column - by default it is in the 'index' column

df = df.reset_index()
df['datetime'] = df['index']
## Uncomment the next line to create local TZ timestamp if needed, using your TZ
df['datetime_local'] = df['index'].dt.tz_convert('America/New_York')

## drop the original 'index' column
df = df.drop(columns = ['index'])

## 5. create mySQL Connection

engine = create_engine("mysql+pymysql://{user}:{pw}@192.168.0.xx/{db}".format(user="root", pw="secretpassword", db="powerwall_mysql"))

## 6. Export data to MySQL

## Using 'method=insert_on_duplicate' prevents errors if the script attempts to copy a duplicate record
df.to_sql('kwh', engine, if_exists = 'append', chunksize =1000, index=False, method=insert_on_duplicate)

engine.dispose()

print("Program Completed")

Set up this Python script on a schedule (using crontab -e on my Pi). I run it every 5 minutes. Set all the same parameters as in the first script. The only difference between the scripts is in the SELECT statement from InfluxDB. The repeating script only selects the last two days' data (you can alter this as needed).

Click to expand Python script

## 1. Importing Modules

from sqlalchemy import create_engine
from influxdb import DataFrameClient

## next section is needed when running incremental updates to handle duplicates
from sqlalchemy.dialects.mysql import insert
def insert_on_duplicate(table, conn, keys, data_iter):
    insert_stmt = insert(table.table).values(list(data_iter))
    on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(insert_stmt.inserted)
    conn.execute(on_duplicate_key_stmt)
    
## 2. Create a client to Connect to Influx DB

client = DataFrameClient(host='localhost', port=8086)
client.switch_database('powerwall')

## 3. Run the query and store results in a dataframe

results = client.query('select * from kwh.http where time > now() -2d order by time')
## (this pulls all data for the last 2 days. Modify as needed based on how often you run the script.

df = results['http']

## 4.. Copy datetime to 'datetime' column - by default it is in the 'index' column

df = df.reset_index()
df['datetime'] = df['index']
## Uncomment the next line to create local TZ timestamp if needed, using your TZ
df['datetime_local'] = df['index'].dt.tz_convert('America/New_York')

## drop the original 'index' column
df = df.drop(columns = ['index'])

## 5. create mySQL Connection

engine = create_engine("mysql+pymysql://{user}:{pw}@192.168.0.xx/{db}".format(user="root", pw="secretpassword", db="powerwall_mysql"))

## 6. Export data to MySQL

## Using 'method=insert_on_duplicate' prevents errors if the script attempts to copy a duplicate record
df.to_sql('kwh', engine, if_exists = 'append', chunksize =1000, index=False, method=insert_on_duplicate)

engine.dispose()

print("Program Completed")

Part 3 - Set up Time of Use cost calculations

• In MySQL, create the TOU table:
• The command below creates price fields with 5 digits after the decimal point. If you need more precision than that, alter the field definitions

Click to expand MySQL query

CREATE TABLE `TOU` (
  `idTOU` int(11) NOT NULL AUTO_INCREMENT,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `days_of_week` varchar(45) DEFAULT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `grid_price` decimal(10,5) DEFAULT NULL,
  `grid_credit` decimal(10,5) DEFAULT NULL,
  `solar_price` decimal(10,5) DEFAULT NULL,
  PRIMARY KEY (`idTOU`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;

• Create entries in the TOU table
  • It may be useful to install MySQL workbench on a machine so you can use the GUI to interact with the TOU table. I am running MySQL Workbench on a Windows machine on my LAN.
  • The days_of_week field is a of days when the price applies. 1=Sunday, 7=Saturday.
    • I am entering it with the days comma separated, but you can use any non-numeric separator or no separator
  • Make sure you don't create two entries for the same date/time/day of the week.
  • You can find overlapping dates and times with this query:

select * from TOU T1 left join TOU T2 on T1.idTOU <> T2.idTOU
where T1.start_date <= T2.end_date and T1.end_date >= T2.start_date 
and T1.start_time <= T2.end_time and T1.end_time >= T2.start_time

• This query does not check for days of the week. So if this query returns any rows where T1.days_of_week contains any of the same days as T2.days_of_week, you may have an issue.

Example of TOU data:

• Update the price data in kwh from TOU. In MySQL:

Click to expand MySQL query

update  kwh as dest,
(select round(solar * solar_price, 10) as solar_total, round(from_grid * grid_price, 10) as grid_total, round(to_grid * grid_credit, 10) as fromgrid_total, datetime_local from kwh 
inner join TOU on TOU.start_date <= kwh.datetime_local and TOU.end_date >= kwh.datetime_local and locate(dayofweek(kwh.datetime_local), TOU.days_of_week) > 0 and time(kwh.datetime_local) >= TOU.start_time and time(kwh.datetime_local) <= TOU.end_time  ) as src
set  dest.cost_solar = src.solar_total, dest.cost_grid = src.grid_total, dest.credit_grid = src.fromgrid_total
where dest.datetime_local = src.datetime_local

• This should populate the pricing for all the existing rows in kwh. If there are issues with your TOU table, you may encounter errors here. I accidentally had an overlapping day where the end_date of one row was the same date as the start_date of the next row, instead of the day before, and it caused this query to throw an error

• Create triggers in the kwh table to update the total cost as the data are inserted and updated.

  • I did this in the MySQL Workbench GUI, but I think these commands are correct to do this from a command window:

Click to expand MySQL queries

CREATE TRIGGER `powerwall_mysql`.`kwh_BEFORE_INSERT` BEFORE INSERT ON `kwh` FOR EACH ROW
set new.cost_solar = new.solar * 
(select solar_price from TOU where start_date <= new.datetime_local and end_date >= new.datetime_local and locate(dayofweek(new.datetime_local), days_of_week) > 0 and time(new.datetime_local) >= start_time and time(new.datetime_local) <= end_time ) ;
set new.cost_grid = new.from_grid *
(select grid_price from TOU where start_date <= new.datetime_local and end_date >= new.datetime_local and locate(dayofweek(new.datetime_local), days_of_week) > 0 and time(new.datetime_local) >= start_time and time(new.datetime_local) <= end_time ) ;
set new.credit_grid = new.to_grid *
(select grid_credit from TOU where start_date <= new.datetime_local and end_date >= new.datetime_local and locate(dayofweek(new.datetime_local), days_of_week) > 0 and time(new.datetime_local) >= start_time and time(new.datetime_local) <= end_time  ) ;

CREATE TRIGGER `powerwall_mysql`.`kwh_BEFORE_UPDATE` BEFORE UPDATE ON `kwh` FOR EACH ROW
set new.cost_solar = new.solar *
(select solar_price from TOU where start_date <= new.datetime_local and end_date >= new.datetime_local and locate(dayofweek(new.datetime_local), days_of_week) > 0 and time(new.datetime_local) >= start_time and time(new.datetime_local) <= end_time  ) ;
set new.cost_grid = new.from_grid *
(select grid_price from TOU where start_date <= new.datetime_local and end_date >= new.datetime_local and locate(dayofweek(new.datetime_local), days_of_week) > 0 and time(new.datetime_local) >= start_time and time(new.datetime_local) <= end_time  ) ;
set new.credit_grid = new.to_grid *
(select grid_credit from TOU where start_date <= new.datetime_local and end_date >= new.datetime_local and locate(dayofweek(new.datetime_local), days_of_week) > 0 and time(new.datetime_local) >= start_time and time(new.datetime_local) <= end_time  ) ;

Part 4 - Add to Grafana

• If you don't already have the MySQL plugin for Grafana, add it

• Configure the MySQL data source:

• Create panels using the TOU data

  • Here is a dashboard with a stat panel and a bar graph panel showing costs:

Final thought:
Combining the 'monthly' charts from the first post above and the accurate TOU historical prices, you can get charts like this:

Here is the JSON for just the two TOU cost panels (stat and bar chart). You should be able to import this into your Grafana and then copy the panels from this to your other dashboards. For a non-PPA you'll want to modify the queries, since the solar would be counted as 'savings' and the total cost is only the 'cost_grid' minus 'credit_grid'.

Click to expand Grafana JSON

{
  "__inputs": [
    {
      "name": "DS_POWERWALL_MYSQL",
      "label": "Powerwall_MySQL",
      "description": "",
      "type": "datasource",
      "pluginId": "mysql",
      "pluginName": "MySQL"
    },
    {
      "name": "DS_INFLUXDB",
      "label": "InfluxDB",
      "description": "",
      "type": "datasource",
      "pluginId": "influxdb",
      "pluginName": "InfluxDB"
    }
  ],
  "__elements": {
    "nvEBTYigz": {
      "name": "Total Cost",
      "uid": "nvEBTYigz",
      "kind": 1,
      "model": {
        "datasource": {
          "type": "datasource",
          "uid": "-- Mixed --"
        },
        "description": "",
        "fieldConfig": {
          "defaults": {
            "color": {
              "mode": "thresholds"
            },
            "decimals": 2,
            "mappings": [],
            "thresholds": {
              "mode": "absolute",
              "steps": [
                {
                  "color": "green",
                  "value": null
                }
              ]
            },
            "unit": "currencyUSD"
          },
          "overrides": [
            {
              "matcher": {
                "id": "byName",
                "options": "http.solar"
              },
              "properties": [
                {
                  "id": "color",
                  "value": {
                    "fixedColor": "yellow",
                    "mode": "fixed"
                  }
                },
                {
                  "id": "displayName",
                  "value": "Solar"
                }
              ]
            },
            {
              "matcher": {
                "id": "byName",
                "options": "http.grid"
              },
              "properties": [
                {
                  "id": "color",
                  "value": {
                    "fixedColor": "purple",
                    "mode": "fixed"
                  }
                },
                {
                  "id": "displayName",
                  "value": "Grid"
                }
              ]
            },
            {
              "matcher": {
                "id": "byName",
                "options": "http.combined"
              },
              "properties": [
                {
                  "id": "color",
                  "value": {
                    "fixedColor": "text",
                    "mode": "fixed"
                  }
                },
                {
                  "id": "displayName",
                  "value": "Combined"
                }
              ]
            }
          ]
        },
        "options": {
          "colorMode": "value",
          "graphMode": "none",
          "justifyMode": "auto",
          "orientation": "auto",
          "reduceOptions": {
            "calcs": [
              "sum"
            ],
            "fields": "",
            "values": false
          },
          "textMode": "auto"
        },
        "pluginVersion": "9.1.2",
        "targets": [
          {
            "datasource": {
              "type": "mysql",
              "uid": "${DS_POWERWALL_MYSQL}"
            },
            "format": "time_series",
            "group": [],
            "hide": false,
            "metricColumn": "none",
            "rawQuery": true,
            "rawSql": "SELECT\n  datetime AS \"time\",\n  cost_solar as \"http.solar\"\nFROM kwh\nWHERE\n  $__timeFilter(datetime)\nORDER BY datetime",
            "refId": "A",
            "select": [
              [
                {
                  "params": [
                    "cost_solar"
                  ],
                  "type": "column"
                }
              ]
            ],
            "table": "kwh",
            "timeColumn": "datetime",
            "timeColumnType": "timestamp",
            "where": [
              {
                "name": "$__timeFilter",
                "params": [],
                "type": "macro"
              }
            ]
          },
          {
            "datasource": {
              "type": "mysql",
              "uid": "${DS_POWERWALL_MYSQL}"
            },
            "format": "time_series",
            "group": [],
            "hide": false,
            "metricColumn": "none",
            "rawQuery": true,
            "rawSql": "SELECT datetime as time,\n  cost_grid - credit_grid AS \"http.grid\"\nFROM kwh \nWHERE\n  $__timeFilter(datetime)\n",
            "refId": "B",
            "select": [
              [
                {
                  "params": [
                    "cost_grid"
                  ],
                  "type": "column"
                },
                {
                  "params": [
                    "cost_grid"
                  ],
                  "type": "alias"
                }
              ]
            ],
            "table": "kwh",
            "timeColumn": "datetime_local",
            "timeColumnType": "timestamp",
            "where": [
              {
                "name": "$__timeFilter",
                "params": [],
                "type": "macro"
              }
            ]
          },
          {
            "datasource": {
              "type": "mysql",
              "uid": "${DS_POWERWALL_MYSQL}"
            },
            "format": "time_series",
            "group": [],
            "hide": false,
            "metricColumn": "none",
            "rawQuery": true,
            "rawSql": "SELECT\n  datetime AS time,\n  cost_solar + cost_grid - credit_grid as \"http.combined\"\nFROM kwh\nWHERE\n  $__timeFilter(datetime)\n",
            "refId": "C",
            "select": [
              [
                {
                  "params": [
                    "cost_solar"
                  ],
                  "type": "column"
                }
              ]
            ],
            "table": "kwh",
            "timeColumn": "datetime",
            "timeColumnType": "timestamp",
            "where": [
              {
                "name": "$__timeFilter",
                "params": [],
                "type": "macro"
              }
            ]
          }
        ],
        "title": "Cost",
        "type": "stat"
      }
    },
    "4QeZTLmgz": {
      "name": "Cost",
      "uid": "4QeZTLmgz",
      "kind": 1,
      "model": {
        "datasource": {
          "type": "datasource",
          "uid": "-- Mixed --"
        },
        "description": "",
        "fieldConfig": {
          "defaults": {
            "color": {
              "mode": "thresholds"
            },
            "custom": {
              "axisCenteredZero": false,
              "axisColorMode": "text",
              "axisLabel": "",
              "axisPlacement": "hidden",
              "fillOpacity": 80,
              "gradientMode": "none",
              "hideFrom": {
                "legend": false,
                "tooltip": false,
                "viz": false
              },
              "lineWidth": 1,
              "scaleDistribution": {
                "type": "linear"
              }
            },
            "decimals": 2,
            "mappings": [],
            "thresholds": {
              "mode": "absolute",
              "steps": [
                {
                  "color": "green",
                  "value": null
                }
              ]
            },
            "unit": "currencyUSD"
          },
          "overrides": [
            {
              "matcher": {
                "id": "byName",
                "options": "Field"
              },
              "properties": [
                {
                  "id": "mappings",
                  "value": [
                    {
                      "options": {
                        "combined": {
                          "color": "text",
                          "index": 2,
                          "text": "Combined"
                        },
                        "grid": {
                          "color": "purple",
                          "index": 1,
                          "text": "Grid"
                        },
                        "solar": {
                          "color": "yellow",
                          "index": 0,
                          "text": "Solar"
                        }
                      },
                      "type": "value"
                    }
                  ]
                }
              ]
            }
          ]
        },
        "options": {
          "barRadius": 0,
          "barWidth": 0.8,
          "colorByField": "Field",
          "groupWidth": 0.7,
          "legend": {
            "calcs": [
              "sum"
            ],
            "displayMode": "list",
            "placement": "right",
            "showLegend": false
          },
          "orientation": "horizontal",
          "showValue": "never",
          "stacking": "none",
          "tooltip": {
            "mode": "single",
            "sort": "none"
          },
          "xField": "Field",
          "xTickLabelRotation": 0,
          "xTickLabelSpacing": 0
        },
        "pluginVersion": "9.1.2",
        "targets": [
          {
            "datasource": {
              "type": "influxdb",
              "uid": "${DS_INFLUXDB}"
            },
            "groupBy": [
              {
                "params": [
                  "$__interval"
                ],
                "type": "time"
              },
              {
                "params": [
                  "null"
                ],
                "type": "fill"
              }
            ],
            "hide": true,
            "measurement": "http",
            "orderByTime": "ASC",
            "policy": "kwh",
            "query": "SELECT \"solar\" * $solar_price, (from_grid - to_grid) * $grid_price as grid, (solar * $solar_price) + (from_grid  * $grid_price) - (to_grid * $grid_price) as combined FROM \"kwh\".\"http\" WHERE $timeFilter ",
            "rawQuery": true,
            "refId": "B",
            "resultFormat": "table",
            "select": [
              [
                {
                  "params": [
                    "solar"
                  ],
                  "type": "field"
                },
                {
                  "params": [
                    "* $solar_price"
                  ],
                  "type": "math"
                }
              ]
            ],
            "tags": []
          },
          {
            "datasource": {
              "type": "mysql",
              "uid": "${DS_POWERWALL_MYSQL}"
            },
            "format": "time_series",
            "group": [],
            "hide": false,
            "metricColumn": "none",
            "rawQuery": true,
            "rawSql": "SELECT\n  datetime AS \"time\",\n  cost_solar as solar, cost_grid-credit_grid as grid, cost_solar+cost_grid-credit_grid as combined\nFROM kwh\nWHERE\n  $__timeFilter(datetime)\nORDER BY datetime",
            "refId": "A",
            "select": [
              [
                {
                  "params": [
                    "value"
                  ],
                  "type": "column"
                }
              ]
            ],
            "table": "kwh",
            "timeColumn": "datetime",
            "timeColumnType": "timestamp",
            "where": [
              {
                "name": "$__timeFilter",
                "params": [],
                "type": "macro"
              }
            ]
          }
        ],
        "title": "Cost",
        "transformations": [
          {
            "id": "reduce",
            "options": {
              "includeTimeField": false,
              "labelsToFields": false,
              "mode": "seriesToRows",
              "reducers": [
                "sum"
              ]
            }
          }
        ],
        "type": "barchart"
      }
    }
  },
  "__requires": [
    {
      "type": "panel",
      "id": "barchart",
      "name": "Bar chart",
      "version": ""
    },
    {
      "type": "grafana",
      "id": "grafana",
      "name": "Grafana",
      "version": "9.1.2"
    },
    {
      "type": "datasource",
      "id": "influxdb",
      "name": "InfluxDB",
      "version": "1.0.0"
    },
    {
      "type": "datasource",
      "id": "mysql",
      "name": "MySQL",
      "version": "1.0.0"
    },
    {
      "type": "panel",
      "id": "stat",
      "name": "Stat",
      "version": ""
    }
  ],
  "annotations": {
    "list": [
      {
        "builtIn": 1,
        "datasource": {
          "type": "grafana",
          "uid": "-- Grafana --"
        },
        "enable": true,
        "hide": true,
        "iconColor": "rgba(0, 211, 255, 1)",
        "name": "Annotations & Alerts",
        "target": {
          "limit": 100,
          "matchAny": false,
          "tags": [],
          "type": "dashboard"
        },
        "type": "dashboard"
      }
    ]
  },
  "editable": true,
  "fiscalYearStartMonth": 0,
  "graphTooltip": 0,
  "id": null,
  "links": [],
  "liveNow": false,
  "panels": [
    {
      "gridPos": {
        "h": 9,
        "w": 2,
        "x": 0,
        "y": 0
      },
      "id": 2,
      "libraryPanel": {
        "uid": "nvEBTYigz",
        "name": "Total Cost"
      }
    },
    {
      "gridPos": {
        "h": 9,
        "w": 5,
        "x": 2,
        "y": 0
      },
      "id": 4,
      "libraryPanel": {
        "uid": "4QeZTLmgz",
        "name": "Cost"
      }
    }
  ],
  "refresh": false,
  "schemaVersion": 37,
  "style": "dark",
  "tags": [],
  "templating": {
    "list": []
  },
  "time": {
    "from": "now-24h",
    "to": "now"
  },
  "timepicker": {},
  "timezone": "",
  "title": "Export 2022-11-01",
  "uid": "txTMWCRRz",
  "version": 6,
  "weekStart": ""
}

[BJReplay]

I'm following along at home. I'm running on Windows 11 on WSL (Ubuntu 22.04.1 LTS upgraded in place from 20.xx) running docker desktop 4.13.1 upgraded from 4.13). In this scenario, Docker Desktop runs in Windows Subsystem for Linux, and so the place for doing the setup for the Powerwall Dashboard is a linux command line, which ends up setting up docker images in another linux VM, and the place for doing things like historical imports, and this set up for MySQL, is that same WSL command line.

I googled mysql docker image x64 and hit https://hub.docker.com/r/mysql/mysql-server which had the instruction docker pull mysql/mysql-server:tag specifying the version tag to get a specific version and docker pull mysql/mysql-server omitting tag to get the latest version - I just grabbed the latest version:

docker pull mysql/mysql-server

This might be semi-platform independent, of sorts.

I followed up with

docker run --name mysqlpw -p 3306:3306 -e MYSQL_ROOT_PASSWORD=mysecret -d mysql/mysql-server

Got to the import phase - had to figure out how to run python interactively (hadn't really done that before - just a script), and ran into this error on the create_engine step: sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1130, "172.17.0.1' is not allowed to connect to this MySQL server"). At first I thought it was because the server wasn't listening on this port, so I tore down the container, and re-started it with an additional parameter to bind all IP addresses, not being able to (at first) figure out how to edit the MySQL config file to force it to listen on all ports from there.

docker run --name mysqlpw -p 3306:3306 -e MYSQL_ROOT_PASSWORD=mysecret -d mysql/mysql-server --bind-address=*

That did get me a step further, but I got the same error on the df.to_sql step. It looks like even though the grant was supposedly for all hosts, I needed to set up a specific user from the host where I was python running the import (the WSL host hosting Docker) for the fix:

CREATE USER 'pwuser'@'172.17.0.1' IDENTIFIED WITH mysql_native_password BY 'pwuserpassword';
GRANT ALL PRIVILEGES ON *.* TO 'pwuser'@'172.17.0.1';

So, that's where I'll stop for the moment - notes on installation on a different platform, and connecting to the newly installed instance to run the install.

I need to think about next steps now, as I have a different pricing structure to @youzer-name, but there's enough to get me going, and the import steps and instructions were enough to get me up and running with a populated hourly database.

Also I'll tag @jasonacox to consider whether or not the options for linux builds of mysql as discussed here https://hub.docker.com/r/mysql/mysql-server are generic enough to consider pulling into a semi-core build that includes MySQL for arm and x64 at least.

[jasonacox]

Thanks @BJReplay ! I love the idea of this as an add-on and @youzer-name work is brilliant!

I would like to get some more confidence that this image would run well on the wide range of platforms the community uses (e.g. Raspberry PI, Linux, VMs, Synology, MacOS, Win11) before making it a forced installed core component. That is why I like the idea of the "add on" setup path. It would allow easy setup and broader adoption and feedback.

For MySQL experts out there who are using this to store all the raw data, I would like to set some expectations around disk usage (yes, I get asked :). InfluxDB seems to need about 400M/year for data preservation (albeit, using downsampling) so we are forecasting a 4G storage requirement for a 10-year lifespan of a Powerwall or 10G for the 25-year lifespan of the Tesla solar warranty. I would assume that if we store all raw data (no downsampling), that requirement would be higher. I'm going to trend MySQL usage to see what it looks like but if anyone has any ideas, please let me know.

As a note, here is an example trend of InfluxDB usage (testing 14 months of data) and you can see the downsampling compression points. I want to post similar MySQL usage as I gather more history.

[BJReplay]

It's a good question!

I have five years of data, and the data in the table is 5MB (as it is hourly data) - 44,000 rows approximately. In summary, the data is insignificant in comparison to the image size and container size, given there is the overhead of a docker image and container. Here's the breakdown of docker on my system - note that the size is listed at 225MB for mysql in the space usage list.

me@myhost:~$ docker system df -v
Images space usage:

REPOSITORY              TAG            CREATED        SIZE      SHARED SIZE   UNIQUE SIZE   CONTAINERS
influxdb                1.8            11 days ago    307.8MB   153.8MB       154MB         1
telegraf                latest         11 days ago    367.9MB   153.8MB       214.1MB       1
jasonacox/pypowerwall   latest         3 weeks ago    61.55MB   5.544MB       56.01MB       1
mysql/mysql-server      latest         3 weeks ago    517.3MB   0B            517.3MB       1
grafana/grafana         9.1.2-ubuntu   2 months ago   366.5MB   0B            366.5MB       1
jasonacox/weather411    latest         2 months ago   61.13MB   5.544MB       55.59MB       1

Containers space usage:

IMAGE                          COMMAND                  LOCAL VOLUMES   SIZE      CREATED        STATUS                  NAMES
mysql/mysql-server             "/entrypoint.sh --bi…"   0               225MB     19 hours ago   Up 19 hours (healthy)   mysqlpw
jasonacox/weather411:latest    "python3 server.py"      0               0B        8 days ago     Up 21 hours             weather411
telegraf:latest                "/entrypoint.sh tele…"   0               0B        8 days ago     Up 21 hours             telegraf
grafana/grafana:9.1.2-ubuntu   "/run.sh"                0               0B        8 days ago     Up 21 hours             grafana
jasonacox/pypowerwall:latest   "python3 server.py"      0               0B        8 days ago     Up 21 hours             pypowerwall
influxdb:1.8                   "/entrypoint.sh infl…"   0               0B        8 days ago     Up 21 hours             influxdb

[youzer-name]

@jasonacox - I was just re-reading this old thread and wanted to give you some feedback on the storage size for MySQL.

For the kwh data, I have 14,288 rows since 2022-02-28, or ~595 days of data. The table is taking up about 2 MB on disk.

After having a major oopsie where I lost my influx database and had to recreate it using the history tool, I now also copy the autogen table over to MySQL as an extra backup. That table has 371,000 records and is using 34 MB on the disk. It only has 15 minute resolution for the dates recovered by the import tool and 1 minute resolution since then. Doing some quick math, if it were all at 1 minute resolution that would be about 48 MB per year.

[jasonacox]

That's awesome! Out of curiosity, would MariaDB be a drop-in replacement for MySQL?

[youzer-name]

From my limited Googling, MariaDB is an improved version of MySQL with some extra features, but unless you're using those extras, they are interchangeable. I'm using MariaDB since it was available for my 32-bit Pi OS, but on my to-do list for this winter is moving PW Dashboard to my second (64-bit OS) Pi and then upgrading this one to 64-bit.

[BJReplay]

Another comment; again, this might be a mysql version issue, but I needed to modify the trigger syntax to get it to work by wrapping it in a BEGIN END block and a delimiter change:

DELIMITER $$
CREATE TRIGGER `powerwall_mysql`.`kwh_BEFORE_INSERT` BEFORE INSERT ON `kwh` FOR EACH ROW BEGIN
set new.cost_solar = new.solar * 
(select solar_price from TOU where start_date <= new.datetime_local and end_date >= new.datetime_local and locate(dayofweek(new.datetime_local), days_of_week) > 0 and time(new.datetime_local) >= start_time and time(new.datetime_local) <= end_time ) ;
set new.cost_grid = new.from_grid *
(select grid_price from TOU where start_date <= new.datetime_local and end_date >= new.datetime_local and locate(dayofweek(new.datetime_local), days_of_week) > 0 and time(new.datetime_local) >= start_time and time(new.datetime_local) <= end_time ) ;
set new.credit_grid = new.to_grid *
(select grid_credit from TOU where start_date <= new.datetime_local and end_date >= new.datetime_local and locate(dayofweek(new.datetime_local), days_of_week) > 0 and time(new.datetime_local) >= start_time and time(new.datetime_local) <= end_time  ) ;
END $$

[BJReplay]

This is where I've ended up (so far).

I have a daily charge as well as a per kWh charge, and a feed in tariff (that varies with time and season). The daily charge far exceeds my energy costs, so is significant in my power bills.

I don't have a solar PPA, so I renamed the solar_cost fields to reflect the daily charge, and changed the update scripts and triggers to update those. They update 1/24th of the daily charge into each hourly bucket.

I haven't yet fiddled around with the savings charts on the standard dashboard, but I'm thinking about whether to do that, and if so, how.

If I set the Grafana dashboard to the same period as my last bill, the numbers almost match:


The reason they don't is that our billing is based off half hourly net metering where a single half hour can only be net import or net export whereas these numbers are based of hourly aggregations and a single hour can be both net import and export, and the little rounding errors end up making a slight difference - but not enough to be significant.

That means that if I use the This Month So Far view in Grafana, I get a very accurate prediction of my current billing period.

[youzer-name]

@BJReplay - Nice! Your solution for the daily charge seems like the simplest way to include that.

I guess if you brought over the raw data you could try to match the 1/2 hour calculations, but it's not worth the effort if the numbers you're getting from this are working for you. I'm definitely happy with these numbers being close to reality, even if they will never be a perfect match.

If we want to make the setup for this more universal, it might make sense to have every possible column in the TOU table - solar_cost, solar_savings, daily_charge, etc. - and then just leave the ones that don't apply blank. If a few more people set this up we may find a few more columns that are needed to cover different scenarios.

[BJReplay]

You're right, and I agonised (for at least 20ms) on whether to rename or add columns, but I figured if I renamed, I could simplify the queries at the other end - for me.

That said, your suggestions do make sense; you add up cost fields and subtract savings fields.

• solar_cost
• grid_cost
• daily_charge_cost
• solar_savings
• grid_feedin_savings

Net = solar_cost + grid_cost + daily_charge_cost - solar_savings - grid_feeding_savings

Solar Savings is still interesting. It doesn't make up a component of matching your bill as issued by your utility, but does make up a component of the value of your solar system.

Whether it is of interest to you or not depends on whether or not you have a PPA.

If you do have a PPA, then it is probably zero (and, instead, you have solar_cost), but your solar system cost you nothing (or a subsidised amount).

If you paid upfront for your solar system, then solar_savings should probably be applying the avoided ToU costs to home consumption provided by solar (as should energy supplied by your powerwall), and those solar savings are what are paying back your investment in solar.

[BJReplay]

I've run with your idea and added back in the solar field - solar_savings. It applies the same ToU rate as the grid cost that would otherwise apply to the energy supplied by solar.

I've updated the triggers so they now have four updates (solar_savings, grid_cost, grid_credit, and daily_charge).

I now show a Bill Cost and an Effective Cost (that includes back in the Solar Savings. This is effectively the payback on my self-funded PPA that I'm getting having purchased my solar system a decade ago.).

For the moment, I haven't considered energy to/from the Powerwall, but I need to review whether solar is gross or net to determine if I need to or not.

I've replaced the savings charts off the standard dashboard with modified versions of those from this discussion.

Final task for this weekend is to implement the change to eliminate negative solar figures overnight.

Many thanks for your example and write up of this - I wouldn't have started without it!

[BJReplay]

Here's my state of play for what I expect my billing period to be (October 31 to November 29 inclusive).

It will be a little bit out because of 30 minute net rules, but this should be very close.

For anyone playing along at home, there was a VPP test dispatch on the 7th of November.

[youzer-name]

@BJReplay

I noticed some errors in the MySQL server logs that were caused by not closing the connection each time the Python script ran to transfer data. I added one line to the Python script and it seems to have fixed the errors.

I'll edit the original posts in case someone doesn't read down to the bottom, but the additional line that needs to be at the end of the script is:

engine.dispose()

[jasonacox]

@youzer-name and @BJReplay - What do you think about creating a spot (folder) in https://github.com/jasonacox/Powerwall-Dashboard/tree/main/tools that would have the scripts and instructions (README) to add MySQL to the project? We could reference this discussion but it would make it easier for the community to find if it was in the repo. Thoughts?

[youzer-name]

Sounds good to me. That would probably be easier than having to scroll through the discussion thread. It looks like @BJReplay made some additions to the scripts that are compatible with the way I originally had it set up. If that's the case, maybe he can give you his version of the scripts with those additional parts.

[jasonacox]

Awesome! Free free to submit a PR or I can do it.

[BJReplay]

@youzer-name @jasonacox happy to. I re-did this a couple of weeks back (I managed to break everything accidently, so had to re-do from the start - oops 😢) but I won't get a chance until mid to late this week the way things are looking like playing out. As it turns out, I have to update rates, and work out how to deal with a new variation on feed in tariff - a certain rate for the first 10kWh per day, and a reduced rate after that (so that's today's job).

[jasonacox]

Thanks @youzer-name and @BJReplay !

I created a placeholder and just copied in the instructions above. You likely want to make some major changes, but this is a start: In the tools README and here: https://github.com/jasonacox/Powerwall-Dashboard/tree/main/tools/mysql

[youzer-name]

After the time change for Daylight Saving in the US earlier today, I noticed the copy from InfluxDB to MySQL was failing. After tracing the issue, there is an error in the process related to the setup of the MySQL table. The "datetime" column in MySQL was created as a 'timestamp' column, but that type of column is timezone aware and that isn't good when trying to store the UTC time. It may not be an issue if the MySQL server is set to UTC, but mine is set to America/New_York and the datetime column was wrong when the clocks moved.

The solution is to change that column from "timestamp" to "datetime". The easiest way to fix existing data is probably to truncate the table, alter the column to be a datetime, and re-import everything. In my case I went through a more complicated process of fixing it in place, since I recently lost my InfluxDB database and had to use the history tool to recreate all the data, so the existing data in my MySQL kwh table should be slightly more accurate than the data imported by the history tool.

I edited the posts above to try to fix this issue. Key updates:

• I added a section to create the kwh table manually after the database is created
• I removed sections where the primary key was added after first import and to where TOU pricing fields were added later in the process, since they are now created with the table
• I changed the initial python script to match the periodic script. The old initial script would delete and recreate the kwh table if it existed. We don't want that to happen since it will recreate the datetime field as a "timestamp" type. The only difference between the scripts now is that the first run selects all kwh data and the periodic script selects only recent data (2 days by default)

@BJReplay, can you update the information in the "Tools" part of the project?

[BJReplay]

That probably also explains why I end up with gaps or overlaps between tariff changes, when I change from one set of dates to another on a midnight boundary in my local time zone.

@BJReplay, can you update the information in the "Tools" part of the project?

Yes, I will update the Tools part of the project - this is still on my to-do list - to write this up properly. I've been putting it off, because I want to include a solution for a volume varying tariff (which I haven't come up with a clean solution to - yet) as part of the write up.

[youzer-name]

The first thing that comes to mind when I try to understand your volume tariff challenge is that the solution will involve building views in MySQL to break the problem down into smaller pieces and then adding the results of the views together to get total cost.

[BJReplay]

Hmmm, I hadn't thought of it that way, but that has given me an idea. I was trying to work out a relatively clean way of doing it in the trigger; it would have involved getting all periods from midnight to the current row for every insert / update, and summing the solar in the to_grid, and working out the quantity > than the volume break, to work out what rate to use.

But having a look at this conversation and forced me to realise that this process relies on the crontab and update-history.py script so that's the point at which to do the volume break calculation - either in the script, or in a separate crontab entry.

If you take the view that the update-history.py script must do at least one day in the past (for my volume break tariff)

results = client.query('select * from kwh.http where time > now() -2d order by time')
## (this pulls all data for the last 2 days. Modify as needed based on how often you run the script.

Then I can do the volume break calculation after the fact for yesterday as an additional step in the script - but that's probably over-kill since I do it every five minutes (thought the query will be pretty fast), or a once an hour or once a day crontab.

Thanks!

Sometimes it just requires a second set of eyes to force you to realise that you don't have to solve the problem the first way you thought you did :)

[youzer-name]

Another comment; again, this might be a mysql version issue, but I needed to modify the trigger syntax to get it to work by wrapping it in a BEGIN END block and a delimiter change:

DELIMITER $$
CREATE TRIGGER `powerwall_mysql`.`kwh_BEFORE_INSERT` BEFORE INSERT ON `kwh` FOR EACH ROW BEGIN
set new.cost_solar = new.solar * 
(select solar_price from TOU where start_date <= new.datetime_local and end_date >= new.datetime_local and locate(dayofweek(new.datetime_local), days_of_week) > 0 and time(new.datetime_local) >= start_time and time(new.datetime_local) <= end_time ) ;
set new.cost_grid = new.from_grid *
(select grid_price from TOU where start_date <= new.datetime_local and end_date >= new.datetime_local and locate(dayofweek(new.datetime_local), days_of_week) > 0 and time(new.datetime_local) >= start_time and time(new.datetime_local) <= end_time ) ;
set new.credit_grid = new.to_grid *
(select grid_credit from TOU where start_date <= new.datetime_local and end_date >= new.datetime_local and locate(dayofweek(new.datetime_local), days_of_week) > 0 and time(new.datetime_local) >= start_time and time(new.datetime_local) <= end_time  ) ;
END $$

@BJReplay

This is going back to a post from November, 2022, and I don't know how I didn't notice this before, but I just spent about an hour trying to figure out why the last day of each TOU period didn't have any cost/credit numbers after midnight.

The end_date comparison in each query needs to refer to the DATE(datetime_local).... not to datetime_local (which includes the time). So for example, for today, 2024-03-30, at 05:00 local, it needs to check that the end_date of the period is less than or equal to 2024-03-30, NOT 2024-03-30 05:00:00.

I had null values for TOU cost calculation every month on the last day of the period for every row after midnight until I fixed it and re-ran the update.

So the triggers should look like:

DELIMITER $$
CREATE TRIGGER `powerwall_mysql`.`kwh_BEFORE_INSERT` BEFORE INSERT ON `kwh` FOR EACH ROW BEGIN
set new.cost_solar = new.solar * 
(select solar_price from TOU where start_date <= new.datetime_local and end_date >= DATE(new.datetime_local) and locate(dayofweek(new.datetime_local), days_of_week) > 0 and time(new.datetime_local) >= start_time and time(new.datetime_local) <= end_time ) ;
set new.cost_grid = new.from_grid *
(select grid_price from TOU where start_date <= new.datetime_local and end_date >= DATE(new.datetime_local) and locate(dayofweek(new.datetime_local), days_of_week) > 0 and time(new.datetime_local) >= start_time and time(new.datetime_local) <= end_time ) ;
set new.credit_grid = new.to_grid *
(select grid_credit from TOU where start_date <= new.datetime_local and end_date >= DATE(new.datetime_local) and locate(dayofweek(new.datetime_local), days_of_week) > 0 and time(new.datetime_local) >= start_time and time(new.datetime_local) <= end_time  ) ;
END $$

The query to fix it looks like this:

update  kwh as dest,
(select round(solar * solar_price, 10) as solar_total, round(from_grid * grid_price, 10) as grid_total, round(to_grid * grid_credit, 10) as togrid_total, datetime_local from kwh 
inner join TOU on
TOU.start_date <= kwh.datetime_local and TOU.end_date >= date(kwh.datetime_local) and locate(dayofweek(kwh.datetime_local), TOU.days_of_week) > 0 and time(kwh.datetime_local) >= TOU.start_time and time(kwh.datetime_local) <= TOU.end_time  
) as src
set 
dest.cost_solar = src.solar_total,
dest.cost_grid = src.grid_total,
dest.credit_grid = src.togrid_total
where dest.datetime_local = src.datetime_local
and dest.cost_grid is null

[BJReplay]

I don't know how I didn't notice this before, but I just spent about an hour trying to figure out why the last day of each TOU period didn't have any cost/credit numbers after midnight.

I wonder if this is because you're in a GMT-x timezone, but I'm in a GMT+ timezone that I'm not seeing the same issue?

Today (11:53am, 31 March, GMT+11), if I run select * from kwh where datetime_local >= '2024-03-31'

I get

Execute:
> select month, year, datetime, datetime_local, cost_grid, credit_grid, daily_charge_cost, solar_saving from kwh where datetime_local >= '2024-03-31' order by datetime_local

+ ---------- + --------- + ------------- + ------------------- + -------------- + ---------------- + ---------------------- + ----------------- +
| month      | year      | datetime      | datetime_local      | cost_grid      | credit_grid      | daily_charge_cost      | solar_saving      |
+ ---------- + --------- + ------------- + ------------------- + -------------- + ---------------- + ---------------------- + ----------------- +
| Mar        | 2024      | 2024-03-30 13:00:00 | 2024-03-31 00:00:00 | 0.0015491570   | 0.0005645041     | 0.0389541667           | 0.0000000000      |
| Mar        | 2024      | 2024-03-30 14:00:00 | 2024-03-31 01:00:00 | 0.0014689733   | 0.0005313538     | 0.0389541667           | 0.0000000000      |
| Mar        | 2024      | 2024-03-30 15:00:00 | 2024-03-31 02:00:00 | 0.0013464807   | 0.0005070144     | 0.0389541667           | 0.0000000000      |
| Mar        | 2024      | 2024-03-30 16:00:00 | 2024-03-31 03:00:00 | 0.0014577384   | 0.0004489937     | 0.0389541667           | 0.0000000000      |
| Mar        | 2024      | 2024-03-30 17:00:00 | 2024-03-31 04:00:00 | 0.0013692715   | 0.0005344986     | 0.0389541667           | 0.0000000000      |
| Mar        | 2024      | 2024-03-30 18:00:00 | 2024-03-31 05:00:00 | 0.0015509363   | 0.0005084738     | 0.0389541667           | 0.0000000000      |
| Mar        | 2024      | 2024-03-30 19:00:00 | 2024-03-31 06:00:00 | 0.0014609919   | 0.0005092725     | 0.0389541667           | 0.0000000000      |
| Mar        | 2024      | 2024-03-30 20:00:00 | 2024-03-31 07:00:00 | 0.0013531315   | 0.0005100443     | 0.0389541667           | 0.0000009296      |
| Mar        | 2024      | 2024-03-30 21:00:00 | 2024-03-31 08:00:00 | 0.0022505260   | 0.0007450007     | 0.0389541667           | 0.0243345894      |
| Mar        | 2024      | 2024-03-30 22:00:00 | 2024-03-31 09:00:00 | 0.0042384623   | 0.0012650856     | 0.0389541667           | 0.0606292890      |
| Mar        | 2024      | 2024-03-30 23:00:00 | 2024-03-31 10:00:00 | 0.0029002220   | 0.0009739162     | 0.0389541667           | 0.0760694503      |
| Mar        | 2024      | 2024-03-31 00:00:00 | 2024-03-31 11:00:00 | 0.0015931408   | 0.0010606438     | 0.0389541667           | 0.1033390971      |
| NULL       | NULL      | NULL          | NULL                | NULL           | NULL             | NULL                   | NULL              |
+ ---------- + --------- + ------------- + ------------------- + -------------- + ---------------- + ---------------------- + ----------------- +
13 rows

It looks like I'm getting data calculated right up to date.

A quirk of being ahead of GMT?

[youzer-name]

This is using local time for everything, so GMT isn't a factor.

Check your MySQL kwh table for the cost information on the last day of any TOU period. If the values aren't null for every row you may already have the DATE() function in your triggers. That, or your TOU periods all end in the future so you haven't encountered the 'last day of the period' issue.

You can also try:
SELECT distinct date(datetime_local) from kwh where cost_grid is null
If you get a list of all of the ending dates of your TOU periods, you have the same issue.

The issue isn't on the 'greater than or equal' side, it's on the 'less than or equal' side that applies to the end date. When the trigger runs, for example, on a row with local_time = 2024-03-31 11:00:00, it is looking for a price to apply where:

start_date <= 2024-03-31 11:00:00
and
end_date <= 2024-03-31-11:00:00
and
start_time <= 11:00:00 and end_time >= 11:00:00

the problem is if the rate period ran from March 1st to the 31st, there would be a TOU entry like:
start_date: 2024-03-01
end_date: 2024-03-31
start _time: 0000
end_time: 2359

...that row would NOT match because 2024-03-31 (the end_date) is NOT greater than or equal to 2024-03-31 11:00:00 (which is datetime_local).

But using 'where ... end_date >= DATE(2024-03-31 11:00:00) which translates to "end_date >= 2024-03-31", the row matches (because 2024-03-31 IS greater than or EQUAL TO 2024-03-31) and the prices are applied.

The reason I never noticed I had a problem is that my last TOU entry always runs from the end of the last billing cycle to 2099-12-31. I have to create a row after I receive my bill and see what the effective rate was for the cycle, then update the tables to update the prices.

So as the data flow in, I never have a day when new data are inserted on the end_date of a TOU period. Since I'm usually only looking at current data or past data over a period of time, I had never looked at a single past date that was the last day of a TOU period and seen that the costs were wrong. I recently made a panel that shows the hourly costs and when I selected a past date range that included the end of a billing cycle, that's where I noticed something was wrong.

p.s. I also realized you don't need the update query I posted above. While it is technically correct, it also isn't necessary because the triggers fire after any update. So if you wanted to update all of the costs for March you can just "UPDATE kwh set cost_grid = 0 where datetime_local >= '2024-03-01' and datetime_local < '2024-04-01'. That edit will cause the trigger to run and all the correct costs will be applied.

[BJReplay]

That, or your TOU periods all end in the future so you haven't encountered the 'last day of the period' issue.

Ahh, OK, that's it.

Not strictly speaking, some have end dates in the past, now, but when active, they've always had an end date in the future, and I've then changed to a new retailer before I've hit the end date of a ToU period. I don't have seasons, so don't have a particularly complex plan.

While it is technically correct, it also isn't necessary because the triggers fire after any update.

I'll update the triggers then just in case. Thanks for mentioning me in the comment.