MySQL data reduction

jonathanb · November 22, 2022, 9:41pm

Hi I have started to get year’s of data in my DB. I don’t want to delete it more average it out to 5 minutes - 1hr depending on the age.

Has anyone got a stored procedure or code that does this nicely?

laursen · November 23, 2022, 10:14am

[jdbc] Better support for Timescale features

opened 03:23PM - 15 Jan 22 UTC

lujop

enhancement

Timescale support was added in #11091 but only for creating hypertables. At lea…st there are two other features of Timescale that fit extraordinarily well with OpenHab: - data compression policies - data retention policies Both are defined per table using a select in a similar way to how the hypertable is created. As there can be also other similar options and new possibilities in new timescale versions I think that will be useful to have a generic option to add extra DDL statements in native format when an Item table is created. That way a user would be able to configure for example something like: post_create_item_table_statements: ``` SELECT add_retention_policy('#tableName#', INTERVAL '24 hours'); SELECT add_compression_policy('#tableName#', INTERVAL '7 days'); ``` If this approach is accepted I will try to contribute with a PR.

jonathanb · November 23, 2022, 1:25pm

Sorry should of mentioned I am using MySQL

denominator · November 23, 2022, 9:02pm

This is not specifically a openHAB issue. Its ok to ask here however you may get a better response and help on a MySQL forum.