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 @manonfgoohttps://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 GitHubhttps://github.com/freol35241/ltss/issues/78#issuecomment-1523902926, or unsubscribehttps://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.