migrate existing recorder Database into ltss

[manonfgoo]

Hello,
it there any simple way to migrate existing recorder Database Data into the ltss table ?

Kind regards

[freol35241]

Hi @manonfgoo and thanks for the questions.

There exists no ready-made script for migration to the ltss table as far as I know. The way to do that will also depend on what backend you are currently running the recorder with, i.e. sqlite, postgresql, mysql, or mariadb.

[manonfgoo]

I am using portgresql From: freol35241 ***@***.***> Reply to: freol35241/ltss ***@***.***> Date: Wednesday, 26. April 2023 at 20:59 To: freol35241/ltss ***@***.***> Cc: manonfgoo ***@***.***>, Mention ***@***.***> Subject: Re: [freol35241/ltss] migrate existing recorder Database into ltss (Issue #78) Hi @manonfgoo<https://github.com/manonfgoo> and thanks for the questions. There exists no ready-made script for migration to the ltss table as far as I know. The way to do that will also depend on what backend you are currently running the recorder with, i.e. sqlite, postgresql, mysql, or mariadb. — Reply to this email directly, view it on GitHub<#78 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/ABTP25F4FDWZC32YHSCAKELXDFWBPANCNFSM6AAAAAAXKD2D7U>. You are receiving this because you were mentioned.Message ID: ***@***.***>

[freol35241]

I will leave this issue open and happily accept contributions for scripts that perform these kind of migrations.

[Bouke]

For postgres, you can do the following:

insert into ltss
select to_timestamp(last_updated_ts) time, states_meta.entity_id, state, shared_attrs::json attributes
from states, states_meta, state_attributes
where states.metadata_id = states_meta.metadata_id and states.attributes_id = state_attributes.attributes_id
    and states_meta.entity_id like 'sensor.%'
on conflict do nothing;

Note that you can optionally limit the entity_id to sensor.% to match your include/exclude list.