search

custom-components / nordpool

This component allows you to pull in the energy prices into Home-Assistant.
442 stars 104 forks source link

Feature: Report current price even if it's the same as last price #223

Closed esplor closed 1 year ago

esplor commented 1 year ago

I don't know if this is HA or Nordpool related, but it seems like the current price doesn't update every hour when the price is exactly the same.

If possible, I would like to force new state even if it is the same state.

Having data pushed from HA to InfluxDB, my Grafana graph looks like this: Missing data at 08:00 and 11:00

Missing data at 16:00

Nordpool sensor data within graph timeframe:

- start: '2022-11-16T07:00:00+01:00'
  end: '2022-11-16T08:00:00+01:00'
  value: 0.195
- start: '2022-11-16T08:00:00+01:00'
  end: '2022-11-16T09:00:00+01:00'
  value: 0.195
- start: '2022-11-16T09:00:00+01:00'
  end: '2022-11-16T10:00:00+01:00'
  value: 0.196
- start: '2022-11-16T10:00:00+01:00'
  end: '2022-11-16T11:00:00+01:00'
  value: 0.195
- start: '2022-11-16T11:00:00+01:00'
  end: '2022-11-16T12:00:00+01:00'
  value: 0.195
- start: '2022-11-16T12:00:00+01:00'
  end: '2022-11-16T13:00:00+01:00'
  value: 0.196

--snip--

- start: '2022-11-16T15:00:00+01:00'
  end: '2022-11-16T16:00:00+01:00'
  value: 0.2
- start: '2022-11-16T16:00:00+01:00'
  end: '2022-11-16T17:00:00+01:00'
  value: 0.2
- start: '2022-11-16T17:00:00+01:00'
  end: '2022-11-16T18:00:00+01:00'
  value: 0.295
- start: '2022-11-16T18:00:00+01:00'
  end: '2022-11-16T19:00:00+01:00'
  value: 0.287
- start: '2022-11-16T19:00:00+01:00'
  end: '2022-11-16T20:00:00+01:00'
  value: 0.259
Hellowlol commented 1 year ago

Im pretty confident that this isnt related to nordpool. We manually write the State each time we update the sensor. You can check the sensor and see the time stamp for when the sensor updated/last changed in dev tools. I would have started there and then check if influx db check for last update or last change

esplor commented 1 year ago

Im pretty confident that this isnt related to nordpool. We manually write the State each time we update the sensor. You can check the sensor and see the time stamp for when the sensor updated/last changed in dev tools.

Of course.. forgot about the timestamp info when clicking on the entity 🤦🏻

I would have started there and then check if influx db check for last update or last change

Thanks, time to rtfm on influxdb and influx integration 😄

esplor commented 1 year ago

Hmm, this is the timestamps for nordpool entity now at 20:11

Sensor data:

  - start: '2022-11-17T19:00:00+01:00'
    end: '2022-11-17T20:00:00+01:00'
    value: 0.2
  - start: '2022-11-17T20:00:00+01:00'
    end: '2022-11-17T21:00:00+01:00'
    value: 0.2
Hellowlol commented 1 year ago

Note to self: https://github.com/home-assistant/core/blob/dev/homeassistant/helpers/entity.py#L441

this need to be set to force the sensor to update. Dunno what the downside will be. Maybe is will spam the db

Hellowlol commented 1 year ago

Can you try https://github.com/custom-components/nordpool/pull/226 ? you can manually change the value in dev tools and check the time stamp after it’s a new hour. Sorry for the bad formatting. I added this pr on my phone

esplor commented 1 year ago

Yes of course, will try it tomorrow.

I have tried experimenting with the template sensor, added a custom attribute with timestamp: hour and hour + minute like this:

    - name: Nordpool Tromsø with hour timestamp_att
      unit_of_measurement: "NOK/kWh"
      state_class: measurement
      attributes:
        timestamp: '{{ now().strftime("%H")}}'
      state: "{{ states('sensor.nordpool_kwh_tromso_nok_3_10_0') }}"

When timestamp was set to hour AND minute the data got pushed to influx every minute, when timestamp was set to above code, data was pushed every hour with a clean line until next value.

Update: Couldn't wait, will post graph tomorrow.

esplor commented 1 year ago

Nice! Here's the result of the raw values after adding line in #226, it works!

The graph is raw data for the last 15 hours, so the points on the line refers to each row in the DB, no spamming :)

2 points between 01:00 and 02:00 was caused by the restart of HA (removal of my template sensors). Don't know why theres a point between 13:00 and 14:00. Could it be the component updating / reloading?

Hellowlol commented 1 year ago

The point between 13-14 is a refresh when the sensors get new data from nordpool api.

About the spamming i was more worried about ha database. That need to be check before i can merge it.

esplor commented 1 year ago

About the spamming i was more worried about ha database. That need to be check before i can merge it.

Of course. Maybe I can help with that, what do you have in mind?

I've queried the state table in HA db/recorder, number of rows in HA is the same as Influx:

sqlite> SELECT * from states WHERE entity_id = "sensor.nordpool_kwh_tromso_nok_3_10_0" AND last_updated > "2022-11-17 23:00";
state_id|domain|entity_id|state|attributes|event_id|last_changed|last_updated|created|context_id|context_user_id|old_state_id|attributes_id|origin_idx|context_parent_id
33855203||sensor.nordpool_kwh_tromso_nok_3_10_0|0.189||||2022-11-17 23:00:00.022537||01GJ3X42CPCWAGE9VF1DDA8HVF||33854559|16457745|0|
33859759||sensor.nordpool_kwh_tromso_nok_3_10_0|0.188||||2022-11-18 00:00:00.011615||01GJ40HY0B0YB4H4K0RR6T8M65||33855203|16461371|0|
33862919||sensor.nordpool_kwh_tromso_nok_3_10_0|0.188||||2022-11-18 00:39:55.856417||01GJ42V1PG0HV3WQJPSTWR9AA9|||16463565|0|
33863396||sensor.nordpool_kwh_tromso_nok_3_10_0|0.188||||2022-11-18 00:44:50.241068||01GJ434161ZWKW96CGF7DQ4660|||16463565|0|
33863687||sensor.nordpool_kwh_tromso_nok_3_10_0|0.188||||2022-11-18 00:44:50.242917||01GJ434162FQSN10KHYG62FPW7||33863396|16463565|0|
33863708||sensor.nordpool_kwh_tromso_nok_3_10_0|0.188||||2022-11-18 00:44:50.326901||01GJ43418PDQ8J760DMYFMZKCW||33863687|16463565|0|
33864935||sensor.nordpool_kwh_tromso_nok_3_10_0|0.188||||2022-11-18 01:00:00.006927||01GJ43ZSM6APXTR5FKHDG3PHE8||33863708|16463565|0|
33869367||sensor.nordpool_kwh_tromso_nok_3_10_0|0.188||||2022-11-18 02:00:00.009254||01GJ47DN89JCCZYJCGA5S1SGJM||33864935|16463565|0|
33873768||sensor.nordpool_kwh_tromso_nok_3_10_0|0.188||||2022-11-18 03:00:00.009228||01GJ4AVGW9HXZFPYQRP9HYDSK0||33869367|16463565|0|
33877972||sensor.nordpool_kwh_tromso_nok_3_10_0|0.188||||2022-11-18 04:00:00.019993||01GJ4E9CGKE0DFQRZDF4PAWWZF||33873768|16463565|0|
33882269||sensor.nordpool_kwh_tromso_nok_3_10_0|0.194||||2022-11-18 05:00:00.018452||01GJ4HQ84JMA0Z97PPHX6PG2VJ||33877972|16478354|0|
33886358||sensor.nordpool_kwh_tromso_nok_3_10_0|0.203||||2022-11-18 06:00:00.012337||01GJ4N53RC6Y8VKAYG4CJS79VX||33882269|16481760|0|
33890443||sensor.nordpool_kwh_tromso_nok_3_10_0|0.209||||2022-11-18 07:00:00.009819||01GJ4RJZC9HAXN7EP21ZQ1CC3T||33886358|16485138|0|
33894979||sensor.nordpool_kwh_tromso_nok_3_10_0|0.206||||2022-11-18 08:00:00.016120||01GJ4W0V0G795G1F7VN19BSYYG||33890443|16488707|0|
33903477||sensor.nordpool_kwh_tromso_nok_3_10_0|0.201||||2022-11-18 09:00:00.008797||01GJ4ZEPM8KPAPYSW7GAYFW3FP||33894979|16493484|0|
33911257||sensor.nordpool_kwh_tromso_nok_3_10_0|0.201||||2022-11-18 10:00:00.014072||01GJ52WJ8EEATRQN7T7KYMJBG8||33903477|16493484|0|
33919567||sensor.nordpool_kwh_tromso_nok_3_10_0|0.199||||2022-11-18 11:00:00.018017||01GJ56ADWJTH1ZGWZZWEK8M1EH||33911257|16503554|0|
33927969||sensor.nordpool_kwh_tromso_nok_3_10_0|0.196||||2022-11-18 12:00:00.011020||01GJ59R9GBVTVEX3RD3R9MM29B||33919567|16509205|0|
33930729||sensor.nordpool_kwh_tromso_nok_3_10_0|0.196||||2022-11-18 12:17:23.045445||01GJ5AR435HF6V0PKHKBK20SAQ||33927969|16510907|0|
33936745||sensor.nordpool_kwh_tromso_nok_3_10_0|0.197||||2022-11-18 13:00:00.013282||01GJ5D654D71JMHTP05DH42RVF||33930729|16514694|0|
33944203||sensor.nordpool_kwh_tromso_nok_3_10_0|0.195||||2022-11-18 14:00:00.014220||01GJ5GM0RECQDGFFM4P5B94AP1||33936745|16519794|0|
33952925||sensor.nordpool_kwh_tromso_nok_3_10_0|0.195||||2022-11-18 15:00:00.012418||01GJ5M1WCCXXPV93KQNTJAAEQZ||33944203|16519794|0|
33961879||sensor.nordpool_kwh_tromso_nok_3_10_0|0.195||||2022-11-18 16:00:00.016259||01GJ5QFR0GMS7SVK3GWPNZSZ11||33952925|16519794|0|

sqlite> SELECT COUNT(*) from states WHERE entity_id = "sensor.nordpool_kwh_tromso_nok_3_10_0" AND last_updated > "2022-11-17 23:00";
COUNT(*)
23
Hellowlol commented 1 year ago

Perfect, do you mind checking the DB for the attrs of the sensors aswell. I guess it will be a lot of data if the attrs are written repeatedly. It will be alof more data than needed.

esplor commented 1 year ago

Looks like it will generate the same data as before. Here are the results before and after the changes. State attrs seems be updated as it was before the change. As long as state value is the same at each hour attrs id will be reused (referred to same id).

--- Before change:

SELECT count(*) FROM states WHERE last_updated > '2022-11-16 02' AND last_updated < '2022-11-17 02' AND entity_id = 'sensor.nordpool_kwh_tromso_nok_3_10_0';

count(*) = 19

SELECT count(*) FROM (SELECT DISTINCT attributes_id FROM states WHERE last_updated > '2022-11-16 02' AND last_updated < '2022-11-17 02' AND entity_id = 'sensor.nordpool_kwh_tromso_nok_3_10_0');

count(*) = 17

-- After change

SELECT count(*) FROM states WHERE last_updated > '2022-11-19 02' AND last_updated < '2022-11-20 02' AND entity_id = 'sensor.nordpool_kwh_tromso_nok_3_10_0';

count(*) = 26

SELECT count(*) FROM (SELECT DISTINCT attributes_id FROM states WHERE last_updated > '2022-11-19 02' AND last_updated < '2022-11-20 02' AND entity_id = 'sensor.nordpool_kwh_tromso_nok_3_10_0');
count(*) = 18

But there are some quirks you may look into:

Examples at 23:00 UTC time:

sqlite> SELECT * FROM states WHERE last_updated LIKE '2022-11-18 23%' AND entity_id = 'sensor.nordpool_kwh_tromso_nok_3_10_0';
state_id|domain|entity_id|state|attributes|event_id|last_changed|last_updated|created|context_id|context_user_id|old_state_id|attributes_id|origin_idx|context_parent_id
34014895||sensor.nordpool_kwh_tromso_nok_3_10_0|0.187||||2022-11-18 23:00:00.051233||01GJ6FGSDKAZ003P58FJ6EXQF5||34006067|16565697|0|
34014898||sensor.nordpool_kwh_tromso_nok_3_10_0|0.187||||2022-11-18 23:00:00.059525||01GJ6FGSDVE757R1MPE8F537AP||34014895|16565697|0|

sqlite> SELECT * FROM states WHERE last_updated LIKE '2022-11-19 23%' AND entity_id = 'sensor.nordpool_kwh_tromso_nok_3_10_0';
state_id|domain|entity_id|state|attributes|event_id|last_changed|last_updated|created|context_id|context_user_id|old_state_id|attributes_id|origin_idx|context_parent_id
34197156||sensor.nordpool_kwh_tromso_nok_3_10_0|0.201||||2022-11-19 23:00:00.018758||01GJ91XGCJFDKJM69MGZ6SX41G||34189774|16683719|0|
34197160||sensor.nordpool_kwh_tromso_nok_3_10_0|0.201||||2022-11-19 23:00:00.021253||01GJ91XGCNPZ6H5VVC5RN5TVSW||34197156|16683719|0|

last_updated vs last_changed:

sqlite> SELECT * FROM states WHERE entity_id = 'sensor.nordpool_kwh_tromso_nok_3_10_0' AND last_changed IS NOT NULL;
state_id|domain|entity_id|state|attributes|event_id|last_changed|last_updated|created|context_id|context_user_id|old_state_id|attributes_id|origin_idx|context_parent_id
33356859||sensor.nordpool_kwh_tromso_nok_3_10_0|1.509|||2022-11-13 08:00:00.011448|2022-11-13 12:24:18.956147||01GHRF578CQMBGFPFGFMKX7JC5||33346177|16098694|0|
33446499||sensor.nordpool_kwh_tromso_nok_3_10_0|0.196|||2022-11-14 12:00:00.006896|2022-11-14 12:15:28.956106||01GHV11RNW5DS4CVZV8KSMJPJJ||33445000|16159362|0|
33562752||sensor.nordpool_kwh_tromso_nok_3_10_0|0.189|||2022-11-15 09:00:00.005065|2022-11-15 12:28:37.767496||01GHXM6J07EYCH5K2N1WGZF2TP||33544737|16244553|0|
33684714||sensor.nordpool_kwh_tromso_nok_3_10_0|0.196|||2022-11-16 11:00:00.008634|2022-11-16 12:28:38.662865||01GJ06K9W66V02N63EA3XKR7TH||33676264|16331152|0|
33803430||sensor.nordpool_kwh_tromso_nok_3_10_0|0.19|||2022-11-17 11:00:00.010777|2022-11-17 12:28:38.036423||01GJ2S008MMW0ZKQT8KZXK4VY8||33796333|16419221|0|

After 2022-11-17, no more last_changed

esplor commented 1 year ago

@Hellowlol I forgot to attach CSV file with the values of states and attributes. Data based on SQL query:

SELECT *
FROM states AS s 
LEFT JOIN state_attributes AS a ON  s.attributes_id = a.attributes_id
WHERE s.entity_id = 'sensor.nordpool_kwh_tromso_nok_3_10_0' AND last_updated > '2022-11-13 02' AND last_updated < '2022-11-20 03%'

nordpool_state_and_attr.csv

Hellowlol commented 1 year ago

A fix has been merged. Closing