Good to hear! I’m curious what your average supply cost was (not including the rebate). I calculated that mine would have been around 11c for the past 30 days, had I been on hourly pricing.
Below shows my supply costs for the past few months. The recent months have been hard with the volatile temps and prices. This June is the highest I’ve ever paid per kWh. My avg since April 2020 has been $0.03 per kWh.
| Jan-22 | Feb-22 | Mar-22 | Apr-22 | May-22 | Jun-22 |
|---|---|---|---|---|---|
| 1811 kWh | 1476 kWh | 1256 kWh | 927 kWh | 949 kWh | 988 kWh |
| $ 64.13 | $ 59.56 | $ 47.85 | $ 43.81 | $ 61.30 | $ 65.73 |
| $ 0.03541 | $ 0.04035 | $ 0.03810 | $ 0.04726 | $ 0.06459 | $ 0.06653 |
Also I can see on my bill that CEJA really impacted the capacity charge rate. It went from capacity obligation x 6.12131 to now capacity obligation x 2.03025. i.e. charge rate is now a third of what it used to be. While capacity obligation mgmt is still a good figure to manage, it’s not nearly as big as what it used to be.
Thanks for the info. Yeah, it’s good to see the capacity charge rate go down by so much. It’s been in the $4.50 - $6.50 range for the past 5 years. But, no guarantees it won’t go back up in the future.
I realized my calculation for my average supply cost (if I was on hourly pricing) was wrong - it would have actually been 9.3c for the past 30 days. So, just barely higher than what I’m paying with my alternate supplier. Going to wait and see and keep watching it over the next few months for now before making the decision to switch back to hourly.
1 Like
Think I fixed my timezone issue. All of HA timestamps are in UTC in the DB which the front end converts to local. The high marker query was definitely including duplicate CDT days because it was breaking days down by UTC.
I added in convert_tz(last_updated, ‘+00:00’, ‘-05:00’) to the below which seams to have solved it (doing the same for the Comed counterpart). Luckily this appears to be a non-issue so far in 2022 as the top 5 entries included no duplicate days. And it would be rare to begin with - the peak would have to hit at 7pm.Regardless, good to fix. I stand corrected. PJM total was okay, but Comed actually had 2 duplicate days from those abnormal heat waves that took place later in the day.
select last_updated, cast(state as integer) as state from (
select *,
row_number() over (partition BY date_format(convert_tz(last_updated, '+00:00', '-05:00'),'%Y-%m-%d')
order by cast(state as integer) desc) as _rn
from states
where entity_id = 'sensor.pjm_total_load_current_hour_high'
and state != 'unknown' and state != ''
and last_updated > date_format(date_sub(current_date(),interval 5 month),'%Y-06-01')
and last_updated < date_format(date_sub(current_date(),interval 5 month),'%Y-10-01')
) as _max
where _rn = 1 order by cast(state as integer) desc limit 1 offset 4
;
How would one go about converting the yaml based sql queries into UI based integrations so I can get rid of the newly discovered sql integrations in the devices page on HA?
Looks like with the most recent database upgrades on 2023.4 don’t like the SQL queries anymore here. I’m getting errors that the SQL query does a full table scan causing instability. The queries will need to be modified it appears - I’ll take a look at some point 
Yep, I updated to 2023.4 today and noticed this as well. I edited my original post with my latest queries that will silence those warnings.
1 Like
There is a warning on the URL PJM Data Snapshot | Instantaneous Load which is used by the custom PJM Sensor from joe248
Data Snapshot will be retired on June 1, 2023. Any systems that currently scrape Data Snapshot for data must transition to Data Miner APIs.
Thanks for letting me know. I updated the PJM sensor to use a different source.
1 Like
Thanks for the fix ! Is there a way to make the sensors to be included long term stats ?
I was just planning on using your sensors to plot graphs and was not going to have any SQL integration.
I think you would just need to set the state_class to measurement for the sensor in your config.
I tried adding that line directly below - platform: and then separately directly below - type:
and both times, there was an error while checking the config
Invalid config for [sensor.pjm]: [state_class] is an invalid option for [sensor.pjm]. Check: sensor.pjm->state_class. (See ?, line ?).
Invalid config for [sensor.pjm]: [state_class] is an invalid option for [sensor.pjm]. Check: sensor.pjm->monitored_variables->0->state_class. (See ?, line ?).
I added the existing sensors to the customize.yaml file and added the state_class there. That works now
I’ve been working on updating the SQL sensors for MariaDB. I think I got them working? Need to wait a few more days to see how data is flowing. I ran into some nuances with the new last_updated_ts because it needed to be converted to human readable timing using from_unixtime but that also converts the timezone which wasn’t done before… I’ll share my updated SQL once I’ve confirmed it’s working.
One thought - I think the SQL queries within the app daemon will need to get updated too in order to utilize ‘states_meta’ and ‘local_updated_ts’. Don’t think it’ll work as of now.
I updated the Appdaemon app gist with the new queries. Let me know if you have any issues.
1 Like
Hourly pricing is great for EVs, solar, and powerwalls. I have my solar nearly always exporting during the 5 peak hours/days, so I usually can get a negative capacity charge, but I don’t want to get too off topic. I haven’t integrated your code yet, but I want to try it. I’m thinking that I can use this to predict the “at risk” days, and then I can have 100% of the solar export and just run on the powerwalls, which means I should be able to double or triple the negative size of my capacity charge…maybe??? My current capacity charge is -2.43kW.
For the noobs who are only looking for the ComEd hourly integration, this is a good support thread: Switching by ComEd kWh supply cost? Sorry for the mini snipe, but I spent hours trying to find useful threads on the ComEd Hourly integration, and this is pretty much the only decent one google finds. I hope this helps some noobs save time if they need something a bit more basic.
So I am trying to install this and unpack how it works. I have only been using HA for a couple weeks, so I am still learning how to navigate the OS.
I have the configuration at the top of the yaml and the sensor data in my sensor section of the yaml. This is accepted as a valid yaml except for the platform: pjm section.
For the appdaemon, what do I do with this? I didn’t find any directions, so I am not sure what I’m supposed to do with this file in HA. I assume that properly installing the appdaemon will fix the yaml error on “Platform: pjm”.
Lastly, I am a bit confused by the sql part. From this thread, it appears that some people are using it and some are not. For my use, all I need is a flag that I can check which will tell me “this might be a peak day” or “a peak day hour might be coming up soonish”, so i can flip my solar and powerwall handling. This doesn’t need to be as accurate as the folks who are losing AC…as I can run the house on powerwalls without issue for as many hours as needed. So do I need the database for this, or is the program/appdaemon still able to run the eco flags without a database?
If I need a database, where does this database come from? I see HA wants a database url on the integration setup, so is this something that I spin up on any hosting site like godaddy? Or is there some add-on where it just puts the database right on the HA harddrive?
Is this integration too hard for a noob? It did take me multiple hours to get the HA ComEd 5 minute pricing integration working with a trigger. 
If the PJM sensor isn’t being accepted in your configuration it’s likely that you didn’t install the custom PJM component correctly.
You shouldn’t need the appdaemon app at all for your use case because you don’t care about nailing the exact hour of the peak and have the ability to run off your powerwalls for long periods of time. The PJM sensor also provides daily load forecast data. You can add the forecast sensors in addition to the instantaneous sensors like this:
sensor:
- platform: pjm
monitored_variables:
- type: instantaneous_total_load
- type: instantaneous_zone_load
zone: 'COMED'
- type: total_load_forecast
- type: zone_load_forecast
zone: 'COMED'
Home Assistant comes with a local SQLite database by default and you shouldn’t need to do anything to set it up as far as I’m aware. You definitely need the 2 “High Marker” SQL sensors for your use case because these keep track of what the highest loads have been so far for the current year.
Once you have the custom PJM component installed and the SQL sensors, you’ll need to create an automation to use them. You’ll probably want something that checks whether the forecast load for the day is greater than, say 98% of the current high marker. If that’s true then there’s a good chance that a new high will be made on that day, so run your house on the powerwalls from say 12pm - 8pm (historically the peaks usually occur between 3-6pm but if you’re able to run as many hours as needed then 12-8pm is safer). Your automation will need to look at both the PJM total as well as the Comed zone loads.