Greetings Everyone,
I`m trying to create Insert statements in order to add historical data to template sensor.
Here is my setup:
Database used: PostgreSQL
All logs are enabled (this will comes in hand in future I hope)
Number Input → gas_input
Template sensor → gas_sensor
Utility gas → GAS:
This is how they looks like in HA:
Lets start with simplest task just to add history of input number - (gas_input_new). (this is not connected/related to anything else because it become more complicated when we add sensor and utility)
I add 2 values: 100 and 200 to see how in each table I`ll get results:
Event_data:
select * from event_data
where shared_data like '%gas_input_new%'
order by 1;
Events:
select event_id, origin_idx, time_fired_ts, data_id, context_id_bin, context_user_id_bin, event_type_id,
encode(context_id_bin, 'hex') as context_id_bin_encoded,
encode(context_user_id_bin, 'hex') as context_user_id_bin_encoded
from events
where data_id in (select data_id from event_data
where shared_data like '%gas_input_new%');
States:
select state_id, state, last_updated_ts,
old_state_id, attributes_id, origin_idx,
context_id_bin, context_user_id_bin, metadata_id,
encode(context_id_bin, 'hex') as context_id_bin_encoded,
encode(context_user_id_bin, 'hex') as context_user_id_bin_encoded
from states
where metadata_id in (select metadata_id from states_meta where entity_id = 'input_number.gas_input_new');
and now the questions:
How these values context_id_bin and context_user_id_bin are generated and is it possible to “remove” them from the manual input?
Is it possible to just update the time_fired_ts to some date in the past without changing anything else in terms of keys to appropriate date when we need this specific value?
Why we have 3 different metadata tables instead of 1?
Sorry for the long post but I can see that this is a topic that is not broadly discussed and I would like to understand it and be able to “manipulate” the data directly in the DB - history is my priority now 
.