Page 1 of 1
General Text Device: which table in the database?
Posted: Sunday 08 August 2021 14:19
by edgarhildering
Ok, I think the subject is pretty clear on my question. My general text device receives device status messages via MQTT and I want to clear these messages if older than 14 days... But where are they in the database
? Or is there a setting so that this is done automaticly
?
kind regards, --Edgar
Re: General Text Device: which table in the database?
Posted: Sunday 08 August 2021 16:34
by HansieNL
I'm using a dzvents script to fill a variable and text device.
The data in the text device will be removed if older than 20 minutes.
Maybe you can use some parts of this script to create your own script:
Code: Select all
-- dzuptime.lua
--
return {
on = { timer = {"every minute"}},
execute = function(dz)
local uptimeTextDevice = dz.devices("Domoticz Uptime")
local uptimeUserVariable = dz.variables("Uptime")
local function deleteTextLog(idx)
dz.openURL(dz.settings['Domoticz url'] .. '/json.htm?type=command¶m=clearlightlog&idx=' .. idx)
end
local minutes = dz.startTime.minutesAgo%60
local days = dz.startTime.daysAgo
local hours = dz.startTime.hoursAgo%24
if days == 1 then DagenText = " dag" else DagenText = " dagen" end
if minutes == 1 then MinutenText = " minuut" else MinutenText = " minuten" end
uptimeTextDevice.updateText(days ..DagenText.. ", " .. hours .. " uur en " .. minutes ..MinutenText)
uptimeUserVariable.set(days ..DagenText.. ", " .. hours .. " uur en " .. minutes ..MinutenText)
if dz.time.matchesRule('every 20 minutes') then -- change to your liking
deleteTextLog(uptimeTextDevice.id)
end
end
}
Re: General Text Device: which table in the database?
Posted: Sunday 08 August 2021 20:41
by edgarhildering
As I understand it you remove the contents of the text variable if it is older than 20 minutes. But I get feedback from different switches and I want to keep these different feedbacks for at least 24 hours. So: every 5-10 minutes the content of the switch changes. I see there's at least a month in the database now and that's a long time...
Or... do I understand it incorrectly?
--Edgar
Re: General Text Device: which table in the database?
Posted: Sunday 08 August 2021 21:45
by edgarhildering
The script helped me after all... I discovered that the table containing the messages is LightingLog; not where the name invited me to look...
I've detected 14000 messages as a result of 1 month of operation.
I think i need to create a script to remove excessive messages from the database...
Thanks for all the help,
--Edgar
Re: General Text Device: which table in the database?
Posted: Sunday 08 August 2021 22:10
by waltervl
I think (but not sure) that these logs will automatically be purged with the setting Lights/switches log (default 30 days)
https://www.domoticz.com/wiki/Applicati ... og_History
Re: General Text Device: which table in the database?
Posted: Sunday 08 August 2021 22:18
by edgarhildering
Although I am not a promotor of changing the database through the back door, here is the statement to do some cleanup.
Code: Select all
delete from LightingLog where DeviceRowID=<insert-ID-here> and date < date("now","-7 days");
Re: General Text Device: which table in the database?
Posted: Sunday 08 August 2021 23:58
by waltervl
Did you check if the log history setting had some impact on the text device log history?
Re: General Text Device: which table in the database?
Posted: Monday 09 August 2021 10:03
by edgarhildering
I did not check it. However, I have my history log settings on 30 days for light switches, short log sensors on 7 days and that is ok. The general text switch turned out to behave like a light switch; i'd rather see it behave as a shortlog sensor!
--Edgar
Re: General Text Device: which table in the database?
Posted: Monday 09 August 2021 11:46
by waltervl
You are free to modify the source code and make a Pull Request (on github) to update