Suggested way to compute average of daily values during past week without statistics sensor?

Configuration
1

Hi: I have been using statistics sensor with the below configuration but it didn’t work very well. It generates random spikes around restart (which is a known issue). My goals is simple: I have a helper input_number that captures the lowest electricity price every day, the value is produced once per day between 13-14 (depending on when tomorrow’s el price is known). My goal is to capture the moving average and use the average as a threshold to control charging of my car (basically it provides a cheap signal to the charger):

- platform: statistics
  name: Cheap Electricity Prices Past Week
  entity_id: input_number.cheapest_electricity_price
  state_characteristic: mean
  unique_id: electricity_cheap_threshold
  sampling_size: 7
#  max_age:
#    days: 7

Can I do the average with another approach in Home Assistant without using statistics sensor?
Many thanks in advance!

2

You can do it with a SQL sensor.

3

Hmm, thanks sounds like a doable solution, however not sure where to start. Need to first find out what query to use to read the past 7 days data from my input_number. Any hint where to start?

4

This will depend on the DB backend you’re using (I’m using Postgres):

Start by identifying the metadata ID for your entity:

select * from states_meta where entity_id = '<your_entity_id>';

You can then query the data from the states table like so:

select * from states where metadata_id = 123 limit 10;

Now you can write the query you need.

1 Like
5

Thanks a lot! Works great!

1 Like
6

Great!

If you don’t mind, you can select my last post as the solution, in order to help others find it.

1 Like