A tag average over time

So we have a tag we will call “Data” that is scanning every minute and that data is getting stored on a historical server. That would be 60 data points every hour. What we are trying to do is create an equation that takes an hours worth of data and finds the average. This would be an ongoing equation so most likely an expression tag would work best here probably.

So the equation would either grab data points from the historian or it would sum up the data every minute then obviously to get the average you would divide by the number of numbers so 60 (hour).

My question is how do you write this equation in a way that ignition understands it?

If you used the tag historian to do the recording, then the tag historian will compute averages (and other stats) for you. Either in a binding for display, or via scripting:

https://docs.inductiveautomation.com/display/DOC81/system.tag.queryTagHistory

If you didn’t use the tag historian, then you generally would use your DB to perform the average for you, using SQL directly (grouping by hour).

This sounds like what I need but looking at the choices I don’t know which one I’d use.

Since you already have it stored in the database, this should be an easy query tag. Assuming your historian is not tag history, this query would give you hourly averages for every hour of the current day (mysql / mariaDB)

select  avg(data) 
from historian_table
where date(t_stamp) = curdate()
group by hour(t_stamp)

can you also change hour to day?

Also historian table? Is that if you called your database Historian because that's what we did.

So this is what it looks like but its not working tag error.

select avg({[.]Water Age Hours})
from historian_table
where date(t_stamp) = curdate()
group by hour(t_stamp)

I have it set as a query tag.

Are you using the built-in tag history logging, or are you storing the tag data with a db connection like Postgre, MySQL, MariaDB, MSSQL, etc?

A DB Connection.

We are able to access it through Microsoft SQL Server Management Studio though.

I know a lot about Ignition except when it comes to dealing with SQL server, I know next to nothing.

It looks like in the historian database there is a table for every month dating back to when we first put this online.

As @pturmel suggested, you will have to use system.tag.queryTagHistory(). You could create a gateway timer script to make the calculation then write it to a tag to access the value.

// Untested code
startDate = system.date.now()
endDate = system.date.addHours(startDate, -1)
avg = system.tag.queryTagHistory(paths=['{[.]Water Age Hours}'], startDate=startTime, endDate=endTime, returnSize=1, aggregationMode="SimpleAverage")
system.tag.writeBlocking(['<PathtoTag>'],[avg])

EDIT queryTagHistory() returns a dataset not a value, so avg would need to be altered to get the value out before writing to a tag.

I guess I do not know what you mean by this?

tagQueryHistory() returns a dataset, not a scalar value. We need to get the value out of the dataset, so it would be like avg[0][1]. [0] is row number, and [1] is column number. I’m not sure if [0][1] is correct.
you can add

print avg[0][1]

to see if the correct value is returned, if not it will print the tag name or whatever else is in the dataset

Since you are using tag history, you have multiple options available, which you use depends on what you are trying to accomplish.

If you want multiple values aggregated to a single time slice(i.e., hourly aggregates for the same Tag of a certain period) then system.tag.queryTagHistory is probably the best scripting option.

If you want history over a certain period to be aggregated into a single value per aggregate then system.tag.queryTagCalculations is probably the best scripting optoin.

However, if you are trying to use this value on a window or view in a property that accepts a dataset, then consider using a Tag History Binding with the Aggregation Mode set to Basic Average.

https://docs.inductiveautomation.com/display/DOC81/Tag+History+Bindings+in+Vision
https://docs.inductiveautomation.com/display/DOC81/Tag+History+Bindings+in+Perspective

2 Likes

So this still did not work for me sadly, still have a tag error. I must have no clue what I am doing when it comes to the query tag.

Unless your tag is of Dataset Type, the write was probably not successful. See @dkhayes117 last post.

Is the tag you are writing to a Query Tag? Because if it is it would write over any write you tried to perform.

Is this script something you’re entering for the Query of a Query Tag, because that would certainly not work.

Yeah I’m using SQL Query. Is there a different or better way?

Also I changed the code back to the select code for now, was trying a few things that didnt end up working.

Also the datasource name is historian so is the from correct?

I have other query tags that work going to a different datasource with the same kind of code.

I’m wondering if the tag is actually getting stored with the tag name Water_Age_Hours?

In our Compliance Datasource all the tags have actual names like East_Reservoir_Effuent_Meter.

There are no actual names that I can find in the Historian database yet all our graphs and tables in ignition pull data from this datasource and display it.

So I’m wondering if ignition gives it a different naming convention or something.

Tables created by the tag historian are not intended to be queried directly with SQL. All of the data is stored by timestamp and tag id, where the relationship between tag id and the tag itself is stored separately. Use the tag historian bindings or scripting calls to access historian data, not your own SQL.

So wait how would you code that out then using the time range I want?

This doesn’t work but I feel like this is close right

startDate = system.date.now()
endDate = system.date.addHours(startDate, -1)
avg = system.tag.queryTagHistory(paths=[’{[.]Water Age Hours}’], startDate=startTime, endDate=endTime, returnSize=1, aggregationMode=“SimpleAverage”)
system.tag.writeBlocking([’’],[avg[0][1]])

I got this in an expression tag.

The first question you need to ask is if you need a tag. What are you trying to accomplish, where is the data going to be used, does it need to be persistent, all of these things (and more) go into determining if you need a tag or not.