Custom Tag History

I have a script that exists in my project library that I use to log tag values to a database. I've tried to incorporate features similar to IA's Tag Historian.

One of these features is the min sample rate and dead band.

To implement these, of course we would need to compare that current time and tag value to the last entry for the tag.

Right now I'm doing this by querying the database to get the last row for the tag and comparing the now time and current value of the tag to the t_stamp and val columns, respectively, to determine if a new row should be added.

It just occurred to me that instead of checking against the database I could use a lastTime and lastVal custom property on the tag to record these values so I'm not querying the database every time I need to determine if the tag change should be logged. I would basically be trading a system.db.runNamedQuery() call each time the tag value changes for a system.tag.writeAsync() call each time the value has changed enough to log the change.

So, something like this

def logTagChange(tag, currentValue, initialChange):

    #logging enabled and not initial change
    if (tag.LogEN and not initialChange):
    
        #trim the OPC path to get the tag name
        PlcTag = tag.OpcItemPath.replace('ns=1;s=['+tag.Device+']','')
        
        #get the value of the last entry for this tag
        lastDBvalue = system.db.runNamedQuery('Project','Last_Tag_Value',{ 'tag_name' : PlcTag })
        numRows = lastDBvalue.rowCount()
        
        #at least one row was retrieved
        if numRows > 0:
            
            #access the most recent time and value
            lastVal  = lastDBvalue.getValueAt(0, 'val')
            lastTime = lastDBvalue.getValueAt(0, 't_stamp')
            
            #calculate the time between now and the last row
            deltaT = system.date.secondsBetween(lastTime, system.date.now())
            
        #no rows in database yet, or large enough change in tag value, or min. sample rate has elapsed
        if (not numRows > 0) or ( abs(currentValue.value - lastVal) >= tag.LogDB ) or ( tag.LogSR > 0 and deltaT >= tag.LogSR ):
        
            #log row in the database
            system.db.runNamedQuery('Project', 'Store_Tag_Change', {'tag_name' : PlcTag, 'val' : currentValue.value)

would change to something like this:

def logTagChange(tag, tagPath, currentValue, initialChange):

    #logging enabled and not initial change
    if (tag.LogEN and not initialChange):
    
        #trim the OPC path to get the tag name
        PlcTag = tag.OpcItemPath.replace('ns=1;s=['+tag.Device+']','')
        
        #access the most recent time and value
        lastVal = tag.lastVal
        lastTime = tag.lastTime
        now = system.date.now()
        
        deltaT = system.date.secondsBetween(lastTime, now)
            
        #no rows in database yet, or large enough change in tag value, or min. sample rate has elapsed
        if ( abs(currentValue.value - lastVal) >= tag.LogDB ) or lastTime == 0 or ( tag.LogSR > 0 and deltaT >= tag.LogSR ):
        
            #log row in the database
            system.db.runNamedQuery('Project', 'Store_Tag_Change', {'tag_name' : PlcTag, 'val' : currentValue.value)

            #update tag values
            system.tag.writeAsync([tagPath+'.lastVal',tagPath+'.lastTime'],[currentValue.value, now])

Thoughts on how the performance might compare?

Unless we're doing something pathological, performance should absolutely be better, because you're eliminating a network hop.

That said, be very careful with your locking and order of operations. You're trading out a lot of battle-tested guarantees for something entirely homegrown.

2 Likes

The first logTagChange script you see above has been running for a year with okay performance. This improvement just came to me as I was reviewing the code again.

Can you explain more about "locking" and order of operations? Not familiar enough with those terms to know what they mean in this context.

This all sort of applies to both versions, but for instance:
What happens if two value changes happen in rapid succession, and the first script has a pending write operation out and the second one adds another? I don't think (someone will surely correct me) that we strictly guarantee writeAsync operations to happen in the order they're submitted.
What happens to your script if you weren't able to retrieve a last value or last time for the tag?
What happens to your logging based on system timestamps if the system time suddenly jumps backwards (can happen with DST events)?

Admittedly, my first post was somewhat kneejerk from the idea of implementing your own 'historian'. What you've got here is nicely narrow in scope, so it will probably be fine.

Ya, daylight savings... man I miss AZ. At worst, we are "missing" an hour of time in the spring and doubling up an hour of time in the fall. I can live with that for the headache it would cause to program around it.

As for the async call order. I'm guessing blocking would just freeze everything up?

Blocking would, by definition, block the tag event script thread handling this. Bad idea. Probably the most "proper" way to do things would be to hold your own concurrent-safe queue of values to process, and 'process' those values separately - but then you're at a significantly greater level of complexity to implement and maintain.

Well, except that there's some sample code to get one started:

1 Like

That's way over my head... will probably not get into that (yet, anyways)

since lastTime will be a number and system.date.now() would return a date, what type casting do I need to do to make that comparison work?

system.date.now().time will be milliseconds since epoch.
Or if you want to save a few nanoseconds, you could import java.lang.System and use System.currentTimeMillis().

Ok, but seconds between needs two dates, so probably get rid of that and just use epoch time everywhere?

now = system.date.now().time / 1000
deltaT = lastTime - now
1 Like

Also, when I pass the entire tag reference to the project script and access via tag.lastVal, is that a qv?

I have no idea, to be honest.

I don't think it is. Seems to be working fine just using tag.lastVal