With configured persistence OpenHAB writes permanently new item states in the data base. That means the data base is permanently growing and it comes a time that you may want to get rid of old item states the data base. There a different methods to do this.

I wrote the python script dbmaintenance.py which is deleting all entries older than a configured number of days. The script uses SQL commands and should work with all SQL databases but is currently only tested with my MariaDB data base. You can find this script in GitHub here: openHABdbTools.

You can copy this script to /etc/openhab2/scripts and give him execution rights with „chmod +x *.py“.

You also need an configuration file dbtools.ini. This file is used to configure your database access and the number of days which you want to keep in your database:

[dbconfig]
#host address of the db server:
host=localhost      	
#openHAB table:
table=openhab   
#openHAB data base user:
user=openhab	
#openHAB data base password
password=openhab		

[maintenance]
#number of days to keep in data base
days=90

Adapt dbconfig to your data base configuration and set the maintenance days to your requirements.

You can call the script now from time to time from the command line. It prints all the items in the data base where it found data older than the configured days and shows how many data it has deleted.

...
[MQTT_R_WateringFrontBalcony,item0351] delete 1 old entries
[MQTT_Q_WateringFrontBalcony,item0352] delete 138 old entries
[MQTT_P_WateringFrontBalcony,item0353] delete 6 old entries
[MQTT_D_WaterSensorFrontBalcony,item0358] delete 2367 old entries
[MQTT_Q_WaterSensorFrontBalcony,item0359] delete 12985 old entries
[MQTT_R_WaterSensorFrontBalcony,item0360] delete 11 old entries
[MQTT_L_WaterSensorFrontBalcony,item0361] delete 2367 old entries
...

To do the maintenance automatically ever 1st day of a month you can use the rule which comes with openHABdbTools in file /openhab2/rules/etc/dbTools.rules.

rule "DBMaintenance"
when 
	Time cron "0 0 0 1 * ?"   // Frist day of a month 0:00 am
then
    val String result = executeCommandLine("/etc/openhab2/scripts/dbmaintanence.py",30000)
    logInfo( "DBTools monthly", result)
end

I would recommend to add a telegram message in the script if you have the telegram binding installed. Just to get a message on your phone with the total number of old entries which are deleted after the script is executed:

rule "DBMaintenance"
when 
	Time cron "0 0 0 1 * ?"   // Frist day of a month 0:00 am
then
   	val telegramAction = getActions("telegram","telegram:telegramBot:mybot")

    telegramAction.sendTelegram("Data base maintenance started.")
    val String result = executeCommandLine("/etc/openhab2/scripts/dbmaintanence.py",30000)
    telegramAction.sendTelegram(result)
    logInfo( "DBTools monthly", result)
end

Would be nice to get feedback if the script is also working for your installation and which persistence configuration you use.