feature request - Time of use pricing graphs

[willplaice]

I have looked - but can't see anything, so forgive me...

I have a time of use tarrif so would like to be able to see cost of energy per day.
I'd obviously have to enter my peak and off peak pricing, and also my generation prices,

I run as much as possible off peak overnight, and hope to survive of solar and storage until the end of the day...

Here in the uk we are likely to experiment with incentives to not use power during the evening peak, so having the ability to have a few customisable TOU pricing windows would be helpful -

[jasonacox]

This is one I could use too @willplaice ! Thanks for opening this. I welcome any influxDB experts to help us figure out a query to divide up the time into those TOU brackets. I have a feeling it is going to be difficult to get a generic solution, but it is worth trying.

Here is what I have for my area (Los Angeles), it is broken up into 4 groups (super-off-peak, off-peak, mid-peak, on-peak) and they vary the rate based on weekday and time of year too (winter vs summer). Yes, they are ridiculous. It would be good to get an idea if there is something similar in other areas:

[BJReplay]

There are more complex versions as well, unfortunately.

Four different price bands is usually the maximum (having said that, true pool price pass through is too complex to consider, but is a thing here in Australia - a feed to pull that in is something that true enthusiasts will have to work out how to include for five minute pricing), and the combination of seasonal pricing and weekday and weekend pricing is part of the equation.

But the added complexity is multiple periods during the day.

I will try to describe it using words rather than an image, but a daily sequence of

• Super Off-Peak
• Off-Peak
• Mid-Peak
• On-Peak
• Mid-Peak
• On-Peak
• Off-Peak
• Super Off-Peak

Isn't totally out of the question.

Storing the configuration for this isn't impossible, but takes a little thought:

• Season Start / End Dates
• Weekday or Weekend or Both
• Start Hour / End Hour
• ToU Band for grid import
• ToU Band for feed in.

And a table of prices
ToU Band for Grid Import / Price
ToU Band for Feed In / Price
Start Date / End Date

This allows prices to change over time, even if the seasonal definition carries on.

Then, at the beginning of each day, work out the set of 24 price bands that apply for that day.

Bucket the energy for each source into each of those hourly bands and apply the appropriate price.

Simple?

Not really, but not super hard.

My Python skills are zero, but my electricity billing / tariff knowledge is very good, so happy to help.

[youzer-name]

I'll explain below how I would do this in MySQL, since my Python skills are limited to cut & paste of other people's Python code. I expect that the Python experts here could achieve this in InfluxDB by periodically running a Python script that updates the data to add the calculated cost to each row in the kwh measurement.

[ I have no idea how to set up the equivalent of the TOU table I used below in Python, but there would have to be somewhere the user could enter the dates and times for the pricing so the script could use them when updating InfluxDB. Can the data be stored in a config (text) file that is easily editable by the user? ]

This can't easily be done directly in InfluxDB for a few reasons:

• Setting up tables for the date, day of week, and time of day brackets doesn't seem like a fit for a time-series database. InfluxDB isn't made for holding the data that would define the pricing parameters.
• As noted in Copy data from InfluxDB to MySQL for monthly charts and Time of Use pricing #82 , InfluxQL lacks any way to select records based on the date part (like where the datetime is in May, or the datetime is a Tuesday, or the datetime is on a Tuesday in May between noon and 8pm). Without that, I don't think you can even start to calculate TOU cost in an Influx query based on the datetime.
• Even if those issues could be solved, coding this into the InfluxDB as a continuous query would make updates by the user when pricing changes a much more difficult task than if the parameters are in a MySQL table or some config file.

I just mocked this up in my MySQL database. I had to go back and re-pull the data with the option to create a local_datetime column in the kwh table, since trying to convert the TOU times to UTC would have been a mess (especially with daylight saving time factored in).

[ tangent: I had to delete an entry with a duplicate datetime for the very first row in my kwh table., I assume this got created when I ran the tool to import missing data. I could tell it was a newer record because it had the 'month' and 'year' columns populated, and none of my other data from that long ago has those fields populated. ]

Here is my TOU table:

Here is some data I inserted just to test if it worked. I set the to_grid_credit at 1/10th of the from_grid_cost just to show the query working.

I am assuming that solar savings can be assumed to be the same as the from_grid_cost, since power from solar offsets pulling power from the grid, but that isn't necessarily true if the power would have otherwise come from the battery. If you knew the battery state of charge and the reserve level you could determine if the solar offset grid use or battery use, but I'm only working with hourly kwh data here, so that wouldn't account for the battery hitting the reserve mid-hour. if you think that it's necessary to run that down, it might be time to try decaf (or Coca Cola without the Coke) 😄

Are there any other parameters that would be needed to calculate the cost for any hour of any day? For users who are on a complicated TOU plan, could you translate the rates into that table?

Here is my query to pull the data. This is set up as a select query. The update version would be a bit shorter since I'm selecting extra columns here just to show the work:

select from_grid, datetime_local, dayofweek(datetime_local) as dayofweek, time(datetime_local) as timeofday,
(select grid_price from TOU where start_date <= datetime_local and end_date >= datetime_local and locate(dayofweek(datetime_local), days_of_week) > 0 and time(datetime_local) >= start_time and time(datetime_local) <= end_time  )  as grid_price,
(select grid_credit from TOU where start_date <= datetime_local and end_date >= datetime_local and locate(dayofweek(datetime_local), days_of_week) > 0 and time(datetime_local) >= start_time and time(datetime_local) <= end_time  )  as grid_credit,
from_grid * (select grid_price from TOU where start_date <= datetime_local and end_date >= datetime_local and locate(dayofweek(datetime_local), days_of_week) > 0 and time(datetime_local) >= start_time and time(datetime_local) <= end_time  ) as from_grid_cost,
to_grid, to_grid * (select grid_credit from TOU where start_date <= datetime_local and end_date >= datetime_local and locate(dayofweek(datetime_local), days_of_week) > 0 and time(datetime_local) >= start_time and time(datetime_local) <= end_time  ) as to_grid_credit,
solar, solar * (select grid_price from TOU where start_date <= datetime_local and end_date >= datetime_local and locate(dayofweek(datetime_local), days_of_week) > 0 and time(datetime_local) >= start_time and time(datetime_local) <= end_time  ) as solar_savings
from powerwall_mysql.kwh order by datetime_local

Here are pieces of the results. You can see the calculated cost for each hour on Wednesday (day 4) is different from Tuesday (day 3) and that the prices change from noon to 8pm on day 2 (Monday):

To make this work in MySQL I would create the cost/savings columns in the kwh table, then create an 'after insert' trigger on the table that runs the calculations each time a new row is inserted.

I don't have a TOU rate plan, so I'm not actually implementing this.

Also, just to complicate things further, I'm on a Solar PPA, and for PPA customers you need to calculate solar cost, not solar savings.

Damn it - just remembered I'm on an escalating price for my PPA, so my cost changes once a year and my current solar cost calculations in the Powerwall Dashboard will not be accurate over time. Looks like I will be implementing, adding one TOU entry per year to capture the date each year when my price increases, and modifying my dashboard panels to use the mySQL data for solar cost.

And final, final thought, I just realized this also applies to anyone who is looking at grid cost historically, even if you don't have TOU. The queries in Grafana are based on a fixed cost. When you go back and look at older data, the totals will all be wrong if the price of grid power changes - which it will.

So getting the correct cost / credit for each kwh entry would allow Grafana to display sum(historically accurate costs), rather than sum(stored kwh * current price) for any time period.

Off to refill my coffee. Need to take my own advice on decaf... 😄

[mcbirse]

Sorry for the off-topic tangent reply:

[ tangent: I had to delete an entry with a duplicate datetime for the very first row in my kwh table., I assume this got created when I ran the tool to import missing data. I could tell it was a newer record because it had the 'month' and 'year' columns populated, and none of my other data from that long ago has those fields populated. ]

I thought this may happen with imported historical data.

Due to the issue with the month tags being wrong before this was fixed (refer #80) probably you have some duplicate data points. Your original data will have the wrong month tag, and then due to the import there will be some duplicate points with correct month tags.

I'd be surprised if your old data wasn't tagged with a month at all? From what I can tell, all data was being tagged even from the very start of this project.

You could check this (I'd be interested to know) - if the below command returns any rows, then you do have data in kwh where the month tag is blank:

docker exec -it influxdb influx -database 'powerwall' -execute "SELECT * FROM kwh.http WHERE month='' LIMIT 1"

It seems it could be important to fix incorrect month tags in all our data, especially if we could be querying the data for other purposes (such as for TOU pricing graphs like this).

I was going to attempt to fix the tags in my data anyway, and started on a python tool to do this (it's not simple). I could share this for addition to the /tools directory again if there is interest?

[BuongiornoTexas]

probably you have some duplicate data points.

This might also be in the history data from the Tesla cloud - I've got a calendar month report that misses one day in the month and has a repeated 30th entry (admittedly with zero data values). There are definitely some oddities in the Tesla cloud responses - which may of course be down to the problem of no documentation from Tesla.

Interestingly, if I run the same report, but include an interval argument to the API, the missing day reappears and the duplicated day vanishes.

[mcbirse]

@BuongiornoTexas - agree, Tesla does some weird stuff.

In this case, I'm pretty sure the reason for the duplicate points in kwh is due to month tags not matching.

kwh retention policy is updated with a SELECT INTO query from autogen. Due to the nature of InfluxDB, the only way to update the value of existing points, is for the timestamp and tags for month and year to be exactly the same.

When there is a mismatch, then an additional data point is added instead (and it can have the same timestamp).

Due to month tags previously not being set correctly for the timezone, if your timezone is e.g. -4 then you would have 4 hours of data in autogen with the wrong month tag. This would translate to a 3 or 4 (?) data points in kwh per month where there would be duplicates (for this example).

Hence, looks like it will be important to fix these wrong month tags in our data. I'll work on that.

[BuongiornoTexas]

In this case, I'm pretty sure the reason for the duplicate points in kwh is due to month tags not matching.

Fair enough - you are definitely the person that knows that part of the data set!

[youzer-name]

@mcbirse I originally started with a different version of a Powerwall data logger that didn't use pyPowerwall (although the month tag comes from a Telegraf.conf setting). That project was not tagging the month. I don't have month tags on my first 4 days or so of data, but no other duplicate dates were created by your import process, only one for the very first timestamp. Not a big deal, I just deleted one of the two. I'm not pressed about the month anomalies, as I'm still not seeing any good way to use the month data in InfluxDB. Sure, if you want your month charts sorted alphabetically by month, there are some charts you can make. I'm sticking with the mySQL replication for now, since it gives me both useful monthly charts and now will give me accurate pricing over time.

I don't think I mentioned it anywhere else, but even if someone can figure out a Flux query that can grab the correct data, it stumbles into some other issues quickly. If you're on a Raspberry Pi and running 32 bit it looks like you're out of luck. If you're running 64 bit and you're willing to switch over to Influx 2.x rather than 1.8, you can probably make it work. But there is no release of InfluxDB 2.x for 32 bit ARMV7, so moving the project to a 2.x database would mean it could only run on 64 bit architecture.

[youzer-name]

Just to connect the dots here, I've posted a time of use pricing solution using MySQL in #82

[poccari]

I'm just playing around with this and working within the framework of what is existing set up.
I added a new datasource in Grafana for the influx 1.8 db, but based it to use the flux query language.
I'm pretty much a Flux noob (and a noob to influx in general), however I have started on a query which is the beginning of a ToU query. I've run into some road blocks, but possibly someone can take this and run a bit further with it?

Using the hourSelection tool, I can get a summary of data from grid, to grid etc

from(bucket:"powerwall/kwh")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) =>
      r._measurement == "http" and
      r._field == "from_grid"
    )
    |> hourSelection(
      start:10,
      stop:15
    )
    |> sum()
    |> group()
    |> sum()

However, the hourSelection() hours are defined in UTC, not in your local timezone (which is a bummer for me because I live in a timezone defined by half an hour: i.e. UTC+9.5). Looking at the documentation, there is an option to supply a "location" argument to the hourSelection filter, but for some reason Grafana, influx, or something doesn't like it, and says:

function does not take a parameter 'location', required params (start, stop)

edit

You can also apply additional filter for weekday/weekend rates etc.

|> filter(fn: (r) =>
    date.weekDay(t: r._time) >= 1 and
    date.weekDay(t: r._time) <= 5
  )

[dJOS1475]

@jasonacox thank you for your hard work, I got this up and running yesterday, and it works perfectly!

My only request is to add ToU pricing options - here in Melbourne, Australia, we just have a simple structure that applies to every day. Peak pricing between 3pm-9pm, and Off Peak everything else.

[BuongiornoTexas]

https://github.com/jasonacox/Powerwall-Dashboard/tree/main/tools/usage-service

The current release specifically caters for the standard Australian ToU structure.

[dJOS1475]

Cheers, I’ll take a look.

[jasonacox]

Thanks @dJOS1475 - appreciate the kind words! 🙏

Definitely check out @BuongiornoTexas great work! Hopefully that will work for you too.

[dJOS1475]

Thanks Jason, I haven’t managed to get it working yet, it seems to default to the admin user 1000 which I have disabled on my server. I’ll have another go at it today.