Program control of Transaction Group delete of old data

Ignition
1

Can a script change the delete older than time of a transaction group?

2

Not directly as far as I know, but the Transaction Group is logging to a standard database table. Why don’t you write a timer script to run occasionally and delete all records from the database table which are older than a set value? You would then be in control of this value and could set it to whatever you wanted.

3

If I’m understanding you correctly, you can make it part of the transaction group. Deleting records older than a time period is already built in.

2015-08-24_6-51-44.png404×713 17.7 KB


Other possibilities exist. Here’s an example of limiting the number of rows in a table:
https://inductiveautomation.com/forum/viewtopic.php?f=70&t=6671&p=19065&hilit=100#p19065
Nothing says it couldn’t be customized for a specific datestamp. :slight_smile:

4

Yes the transaction group can delete records older than the set time. However, I would like a client to be able to control how long to keep records. The oldest time in the transaction group can apparently only be changed by the designer.

5

This is where Ignition’s tight integration with SQL databases really shines! You can write a script to do exactly this, running a SQL query likeDELETE FROM TableName WHERE date<'2015-03-15'You can either let the users select the date using a control (with appropriate safeguards!) or calculate it automatically to be for example 6 months before the current date. This script can then be run manually from a button or automatically from a timed script.

6

I do something similar where I clear records that are older than X number of months (controllable with the function). Here is the function that I wrote:

[code]def tableScrub(tableName, timeColumn, numMonths):
“”"
Scrubs a table of data older than the number of months passed into the fuction
tableName = The table name in the database to scrub
numMonths = The number of months to keep data for
“”"

import system
scrubLogger = system.util.getLogger("Database Table Scrubbing")
from java.util import Calendar
scrubDate = Calendar.getInstance()
scrubDate.add(Calendar.MONTH, -numMonths)
	
query = "DELETE FROM %s WHERE %s <= ?" %(tableName, timeColumn)
	
try:
	rowsAffected = system.db.runPrepUpdate(query, [scrubDate.getTime()])
	scrubLogger.info("%s rows were scrubbed from database table %s" %(str(rowsAffected), tableName))
except:
	scrubLogger.error("Data Scrubbing encountered an error deleting data from table: %s, time column: %s, date: %s" %(tableName, timeColumn, scrubDate.getTime()))[/code]

Then I call the function in a gateway timer script:

# Clear old data from ABB inverter fault log shared.genFunctions.tableScrub("ABB_Inverter_Faults", "t_stamp", 12)

Perhaps that will be useful for you, perhaps not. Either way it displays an example of how to clear out old data your own way.

7

I know I can write a script, in fact I did. I was trying to use functionality already built into the transaction group to simplify the application. However, it looks like the script is the best solution.

Thanks to all for the suggestions. I will consider this closed.