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:
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)
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.
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.
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.
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.