Savings Update for TOU 4pm to 9pm

[Flying-Ace]

Hi, need help with a query statement for sum of a 4pm to 9pm blocks per timeFilter (ie: 2 separate time filters).

Current Stock Query is the timefilter for grafana.
SELECT integral("to_grid") / 1000 / 3600 FROM "autogen"."http" WHERE $timeFilter tz('America/Los_Angeles')
the current cost filter powerwall to home, or solar to grid doesnt capture exactly the periods of 4pm to 9pm.

Desired: similar query but for the 4pm to 9pm period block, WHERE $timeFilter.
ex: if time filter is 2days, i want the integral/sum of the todays 4pm-9pm & yesterday's total of from 4pm-9pm.
ex: i want to know how much net usage for during my tou peak period for the entire month.

[jasonacox]

Thanks for opening this discussion @Flying-Ace . I updated the subject to what I think you are talking about. I agree with you. We need to update the query for savings data to accurately represent TOU pricing. Currently it uses just ONE rate.

I welcome help from any Grafana + Influx expert to suggestion any edits or approach. I'll add it to my backlog to research as soon as I have some time.

[BMWE85]

I have the same kind of Issue. I do not get any Credits for the time between 9:00am and 4:00pm. I did some research but haven't found anyway to implement this kind of query. But I'm not really an Expert here so I hope there is somebody with more experience who can give me a hint hot to do it.

[metaton8086]

To expand on this, my electric rate with my co-op varies by year and usage. The first 600kWh is the same all year, but the rate after 600kWh is lower from the months of Nov-April. The credit is always the same.

[jasonacox]

Thanks for the use cases. It is clear that there are a lot of different ways that utility companies are going to charge. That presents a complex challenge for us to have a generic way to approach this. I would love any ideas. There has been a suggestion to add MySQL to the project to make it easier to construct complex queries (see #118). But we would still need a way to adapt to local use cases.

Tesla seems to have a way to do this. Do you all see accurate savings details in the Tuya app that reflect the various equations your electric company uses?

[BJReplay]

Tesla seems to have a way to do this.

Well, to a certain extent; there's an ongoing discussion on a local Australian forum about how badly they have missed being able to do this for a whole lot of common tariffs (two different peak periods, two different shoulder periods, three different off-peak periods, for example - simple not supported), let alone variable pricing; we have half-hourly variable pricing (for both import and export) and soon to be 5-minute variable pricing) available to residential customers.

[jasonacox]

Yuck. About the only "clean" way I can think about doing this is to record the users/homeowners "at that moment" kWh energy price in InfluxDB as a sample in the time series data along with the current energy data.

Ideally it would be a feed from the utility company (API call?) that says "at the moment" your energy is costing you X. I doubt any are providing that (but would be nice!), so we would have to build a compute service that looks at "time of day", "time of year", "kWh from grid today", and whatever other data points utility companies are using, to compute "your cost of energy at the moment" and stuff it into InfluxDB. Complication of that service would be how we allow the user to define the matrix/equation for their particular utility tariff scenario.

Once we had the time series data in InfluxDB along with the energy data, we could compute the running cost and savings (e.g. integral of cost(t) * energy(t)).

[BuongiornoTexas]

I've been thinking about this issue in the background for a while now. Our utility (Aurora) does provide an API. Which, as you would expect, is 100% undocumented, and I'm contemplating learning how to sniff the app API calls. But even if I can sort this out, it's useful for < 0.0000001% of the user base, and to do it properly, we'd need one API per utility. So I don't think that's going to be the best path.

Right now, I agree with @jasonacox that the best way to do this is probably add this data into the InfluxDB. But, we can take a leaf out of the utilities book and recognise that this data probably doesn't need to be updated continously for most users (my utility updates this data between 24h and up to a week late! -We can do better though!).

I'll outline the recipe I'm thinking about for the common tariffs of peak/shoulder/off peak periods with varying seasons, but I think can be adapted to @metaton8086's case. (variable cost is harder - would require a dynamic feed from the utility!)

As @Flying-Ace has discovered, InfluxQL does not support periodic integrals. This approach works by eliminating the problem of breaking the view window into tariff periods. Instead it integrates over the whole window for each tariff type, and we manage the windows by setting the tariff value to zero whenever the tariff is inactive. So we need a separate query for each tariff period.

So far, I've only got as far as the thinking, because dealing with InfluxQL gives me an instant headache, so I go and do something else more fun.

First up, assume we have a table of tariffs of this sort of form:

Season start date-time, tariff 1 start time, tariff 1 rate, ....,, export tariff rate 
Season start data-time, ...1

# example for my off peak/peak/off peak/peak/export 
2022-10-01-02:00:00.000, 00:00, 0.13, 00:80, 0.33, 00:11, 0.13, 00:17, 0.33, 0.09

I'm assuming only one export tariff rate, but if this is variable, it could be added to the table as well.

There is also the issue of how to value self consumption - my first pass on this is to assume that house self consumption from either battery or solar is worth a credit equivalent to the tariff rate at the time, and that battery charging has a zero value (value only seen when used for self consumption).

The we run a batch influx query every once in a while. Once an hour should be a good starting point, but we can make it user configurable.

Rough pseudo code for the batch update (idea 1, lots of work for Grafana integrals, I don't like, see next post for cleaner alternative):

• Check the time of the last batch update and add tariff data to any new records.
• Use a variant of @mcbirse method to write the active tariff rates into each line of autogen. To make the integrals work, we need to write in both the active tariff rate and ALSO zero values for any inactive tariffs. So if if the autogen record date-time corresponds to a 13c tariff in the second period out of 4, with a 9c export tariff, we add the following tariff data to autogen , 0, 0.13, 0, 0, 0.09.
• We can then run grafana queries of the following form to generate cost and savings information for the period:

# export income
SELECT integral("export_to_grid" * export_rate) / 1000 / 3600 FROM "autogen"."http" WHERE $timeFilter tz('America/Los_Angeles')
# Period 1 import cost, self consumption saving
SELECT integral("grid_import" * tariff_1_rate) / 1000 / 3600 FROM "autogen"."http" WHERE $timeFilter tz('America/Los_Angeles')
SELECT integral("home_self_consume" * tariff_1_rate) / 1000 / 3600 FROM "autogen"."http" WHERE $timeFilter tz('America/Los_Angeles')
# Period 2 import cost, self consumption saving
SELECT integral("grid_import" * tariff_2_rate) / 1000 / 3600 FROM "autogen"."http" WHERE $timeFilter tz('America/Los_Angeles')
SELECT integral("home_self_consume" * tariff_2_rate) / 1000 / 3600 FROM "autogen"."http" WHERE $timeFilter tz('America/Los_Angeles')
...

(I'm hand waving the influxQL energy flow calcs, but you get the idea). Note that I'm doing this as multiple queries, because everyone I know who has this setup is interested in how much power cost is for each tariff period (well most especially the peak periods!).

My next post has a some what cleaner version of this that works by pre-calculating the integrals and then just summing costs.

[BuongiornoTexas]

The second version is a bit cleaner and is based on sums rather than integrals in grafana.

• Create a 5 minute version of cq_kwh, which can gather kwh hour consumption in 5 minute intervals in the load, export, and import categories. We do integrals here, but we do them once to create 5 minute interval power usage data.
• We also add the tariff information to the cq_5min_kwh per the previous post.
• We can then run sum based queries in grafana to gather view window cost and savings data.

# export income
SELECT sum("export_to_grid" * export_rate) FROM "cq_5min_kwh"."http" WHERE $timeFilter tz('America/Los_Angeles')
# Period 1 import cost, self consumption saving
SELECT sum("grid_import" * tariff_1_rate) FROM "cq_5min_kwh"."http" WHERE $timeFilter tz('America/Los_Angeles')
SELECT sum("home_self_consume" * tariff_1_rate) FROM "cq_5min_kwh"."http" WHERE $timeFilter tz('America/Los_Angeles')
# Period 2 import cost, self consumption saving
SELECT sum("grid_import" * tariff_2_rate) FROM "cq_5min_kwh"."http" WHERE $timeFilter tz('America/Los_Angeles')
SELECT sum("home_self_consume" * tariff_2_rate) FROM "cq_5min_kwh"."http" WHERE $timeFilter tz('America/Los_Angeles')
...

Anyone who is happier working in InfluxQL than I am is welcome to see where these ideas go. Otherwise, watch this space, and I'll get to trialling it in due course.

[BJReplay]

I would like to say I'm following along, but despite the fact that I did pure and applied maths (and that's showing my age), I'm not following along. Perhaps it is still too early in the morning on a Public Holiday.

I still haven't written up the MySQL approach because that also requires thinking, and I'm blocked on how to solve my current problem which is - while the MySQL approach first written up by @youzer-name was working brilliantly for me, now that I have a volume varying tariff (first 10kWh per day feed in at one rate, residual at another rate) it does not, and I'm yet to work out a clean solution for that problem either. I know I can, it is just how to work out how to do it cleanly that I haven't yet worked out. It's the sort of problem that I will wake up one morning, having solved overnight, it is only that my subconscious SQL brain is obviously on holiday mode.

But, I only mention that because it sounds like this approach goes some way towards solving both.

It still seems like you need a table of tariffs, but combine that table of tariffs and some InfluxQL and 🤷

[BuongiornoTexas]

Apologies - this was very much an info dump as it seemed a good place to collect my thoughts.

On the easy part of your comment, with the way many Australian tariffs are structured, you just can't avoid a tariff table - you'll find these in the Tesla API if you go looking. And from a DB perpspective, they are pretty easy (at least as long as we aren't dealing with dynamic, variable cost).

The bigger problem is that the InfluxQL integral function can't be used on discrete intervals in a time window. So if I just want to integrate a value such as power import between 4 and 9pm for everyday in 7 day period, I cant' construct that as a single query - I need a query for each individual period in the time window (and multiply this by three if I want import, export and self consumption savings).

But I can make the integral work by creating a set of new variables which are calculated as:

tariff_cost_rate_x_y = instant_power_flow_y * tariff_ratex_x * tariff_flag_x

Where y is import/export/self consumption, x is peak, off peak, shoulder or export, and tariff_flag is 0 when the tariff window x is active, and 0 otherwise.

If I do this, tariff_cost_rate_x_y has continuous values through the interval, but they are zero when the tariff is not active. So we can now do a single integral per tariff period (admittedly by three for import/export and self consumption), but the big thing is that this works on windows of arbitrary length without needing to break up the intervals.

I got a bit lazy in my first post and combined some of these elements, but it's the same principal @jasonacox already uses for import/export segregration in the data - we carry both import and export values, but one of them is always zero!

(I'm too lazy to learn how to export to MySQL and do the processing there!)

[BJReplay]

No need for apologies! I appreciate you writing this up.

[BuongiornoTexas]

No need for apologies! I appreciate you writing this up.

No probs - did the follow up make more sense?

[BJReplay]

Yep

[BuongiornoTexas]

Minor update from me - I've managed to pull together a flux based ToU example - I've put it in separate discussion, as I don't think it is a good long term solution to this problem (#225)