Batch receive time series data

Development
1

I have a data provider that exposes the previous day’s data at (roughly) midnight.
I would like to ingest this data as if it had been received live.

I’ve tried creating a sensor - but it doesn’t appear Home Assistant has any ability to provide a timestamp for reference with the data. The value is the value, now.

I’ve tried MQTT publishing the data - but again - can’t seem to travel in time.

Some example data:

1581123600 - 1581125400 - 0.1351
1581125400 - 1581127200 - 0.0873
1581127200 - 1581129000 - 0.1017
1581129000 - 1581130800 - 0.0922
1581130800 - 1581132600 - 0.1224
1581132600 - 1581134400 - 0.128
1581134400 - 1581136200 - 0.12
1581136200 - 1581138000 - 0.1319
1581138000 - 1581139800 - 0.1193
1581139800 - 1581141600 - 0.1275
1581141600 - 1581143400 - 0.139
1581143400 - 1581145200 - 0.161
2

Some more examples of the data are below.
There is no way to bring this data in “live” as the power readings are not available until the next (sometimes next business) day.
Surely there is a way to bring in Time Series data when it’s not available live?

        '10':
          metadata:
            has_solar: false
          interval_range:
            starts_at:
              time: '05:00'
              date: '27'
              day: Sunday
              month: September
              day_short: Sun
              month_short: Sep
              yyyymmdd: '20200927'
            ends_at:
              time: '05:30'
              date: '27'
              day: Sunday
              month: September
              day_short: Sun
              month_short: Sep
              yyyymmdd: '20200927'
          data:
            total_spend: 0.1488
            average_spend: 0.1703
            consumption: 0.1488
            export: 0
        '11':
          metadata:
            has_solar: false
          interval_range:
            starts_at:
              time: '05:30'
              date: '27'
              day: Sunday
              month: September
              day_short: Sun
              month_short: Sep
              yyyymmdd: '20200927'
            ends_at:
              time: '06:00'
              date: '27'
              day: Sunday
              month: September
              day_short: Sun
              month_short: Sep
              yyyymmdd: '20200927'
          data:
            total_spend: 0.1422
            average_spend: 0.17
            consumption: 0.1422
            export: 0
3

I was just wondering the same thing. I still don’t see any way to do this.

1 Like
4

I don’t think there is… but it also seems there’s no desire to implement something like this.
It’s a little frustrating with the “Energy” push in the current version - there are millions of people that have access to their energy data, but it’s delayed by 24 hours.

1 Like
5

You could implement that by changing the core and it looks worthwhile.

The simplest would be a new method on the recorder interface that would insert this data into the state history table. E,g, batch_load_state

You could also, just connect to Postgres and stick it in the table (assumes you are using Postgres for recorder)

Feeding it into the state machine as current values will be problematic because HA is not a change based system it is a current state system. For example if the state updates 3 times very quickly, the clients will likely only see the last value. AFAIK there is no good way to provide a queue of state changes in the system

1 Like
6

I’d like to see a method to attach a timestamp to a value.

Seems like the last_reset is getting close

1 Like
7

Hi @troykelly I am also interested in this batch loading with “short-time” historical data.

I read the documentation regarding last_reset_topic and last_reset_value_template and I must admit, I don’t understand it.

Did you (or someone) sucessfully employ these methods? A real-word example would be very helpful.

Best & thanks already for any help

8

Hi there @mpschr
Sadly - there is no dev/community interest in adding this, and I barely have time to blink my eyes at the moment - so I can’t really contribute anything of value.
I’ve not come up with a creative way around it either.

9

Hi guys,

I am working on DIY sleep/breath monitor and figured out that I don’t need to send data from ESP32 in realtime, and decided to implement a batch sync in the morning.
Thus, I faced this issue too. And looks like I just found a method to attach a timestamp to a value

It looks quite simple - you just have to send a bunch of events like this, with your entity id (which should be already created in system), state value, and last_updated timestamp.

event_type: state_changed
  event_data:
    entity_id: sensor.3c71bf4822b8_i2saccoef
    new_state:
      entity_id: sensor.3c71bf4822b8_i2saccoef
      state: "1.4"
      last_updated: "2022-07-25T23:00:25.082925+00:00"

I tested this and it seems I’m able to insert into recorder database the “data in the past”.

Hope it would be helpful for somebody.

15 Likes
10

Yes! Thank you, I’ve been struggling with this.

So I’m clear on how this works:

  • data import automation runs in the morning
  • add yesterday’s sum to the running total (this is what I do now, and the statistics say everything happened today at 6am), or will the hourly events add to the total?
  • loop through the hourly data, emitting events for each hour
  • emit one more event to subtract the subtotal from this morning’s 6am data from the statistics?

In my case, the daily data is added to an input_number that feeds a template sensor, and is reset to 0 monthly on the billing date

11

Hi thanks for this! Does this still work? I’m getting errors when I try it and the states in the past are not pushed through.

That said, I’m testing firing the state_changed event from an automation and wonder whether that could be the problem.

12

I got this to work by calling the state_changed event from the dev tools. I sent this payload:

entity_id: input_number.test_number
new_state:
  entity_id: input_number.test_number
  state: "2.3"
  last_updated: "2023-12-19T03:00:00.1234+00:00"

I was able to set data for dates both in the past and the future. Although it did not update the value in the UI, it did update the history graph in the recorder, which is mostly what I care about.

Edit: See my thread for more details, but as of now, I think it’s not possible to send these Events from scripts or automations. It seems to only work from the dev tools. This is due to how HA is storing the new state as a Python Dictionary instead of a HA State object when it puts the Event on the bus.

2 Likes
13

If helpful, I posted some reasonably robust Python code to insert backdated state data into the states table. The source can be either a CSV file or a SQLite db – with 2 columns (one for timestamp in UTC, other for data to be entered)

https://community.home-assistant.io/t/enter-delayed-aka-backdated-mqtt-data-asynchronously-based-on-attached-timestamp /750485/7?u=puterboy

1 Like
14

Note: See the following link for updated version that also adds the corresponding statistics to the statistics and short_term_statistics tables.