There are to tables holding state changes, events and states.
The sensor values are in states.state contains sensor value and events.time_fired is the timestamp but what are actually the other timestamps in states-table?
Table "public.events"
Column | Type | Modifiers
------------+--------------------------+-----------------------------------------------------------
event_id | integer | not null default nextval('events_event_id_seq'::regclass)
event_type | character varying(32) |
event_data | text |
origin | character varying(32) |
time_fired | timestamp with time zone |
created | timestamp with time zone |
Indexes:
"events_pkey" PRIMARY KEY, btree (event_id)
"ix_events_event_type" btree (event_type)
Referenced by:
TABLE "states" CONSTRAINT "states_event_id_fkey" FOREIGN KEY (event_id) REFERENCES events(event_id)
Table "public.states"
Column | Type | Modifiers
--------------+--------------------------+-----------------------------------------------------------
state_id | integer | not null default nextval('states_state_id_seq'::regclass)
domain | character varying(64) |
entity_id | character varying(255) |
state | character varying(255) |
attributes | text |
event_id | integer |
last_changed | timestamp with time zone |
last_updated | timestamp with time zone |
created | timestamp with time zone |
Indexes:
"states_pkey" PRIMARY KEY, btree (state_id)
"states__significant_changes" btree (domain, last_updated, entity_id)
"states__state_changes" btree (last_changed, last_updated, entity_id)
Foreign-key constraints:
"states_event_id_fkey" FOREIGN KEY (event_id) REFERENCES events(event_id)
states has timestamp columns last_changed, last_updated, created. I guess created is only technical value?
Created-column also show values that are -3 hours compared to others, so maybe there’s something wrong with it’s time zone code?
In history-component there’s code that uses both last_updated and last_changed.
"""Return states changes during UTC period start_time - end_time."""
states = recorder.get_model('States')
query = recorder.query('States').filter(
(states.last_changed == states.last_updated) &
(states.last_changed > start_time))
if end_time is not None:
query = query.filter(states.last_updated < end_time)
So actually the query is:
states.last_changed == states.last_updated and
states.last_changed > start_time and
states.last_updated < end_time
Because last_changed = last_updated, the query can also written in a more easy way?
states.last_changed == states.last_updated and
states.last_changed > start_time and
states.last_changed < end_time
Now it’s a little more intuitive?
I tried creation of partitions (daily or monthly) in Postgres but there wasn’t any gains, actually the opposite. I tested with 4000 rows/day, data for year = 1,5M rows. If you are purging old records, then it would make more sense to just drop old partitions.