Average Value Missing End of Data When Querying Historian Tag

Hi,

I'm wondering if anyone can help with a problem I've been having. I'm using Ignition 8.1 and Vision.

I have Ignition set up to monitor PLC tags from a production machine. Of these tags, I am historically trending a "Machine_Running" tag. This tag is either 0 (not running) or 1 (running).

I am using this tag to determine machine uptime (avialability) during a specific product timeframe. To do this I am providing a start / end time and tag name into the queryTagHistory() function. See below for script I am using:

value = 0

startTime = '2022-07-14 19:26:10.260' #Start time of product.
endTime = '2022-07-18 07:05:40.067' #End time of product.

dataSet = system.tag.queryTagHistory(paths=['machine running'], startDate=startTime, endDate=endTime, returnSize=1, aggregationMode="Average", returnFormat='Wide')

#Get value from returned dataset.
value = dataSet.getValueAt(0,1)

#Write the average value to the text field.
event.source.text = str(value)

The script above outputs an uptime / availability of 0.713 (71.3%).

The problem I am getting is that the value returned does not match the true uptime / availability throughout this timeframe.
If I plot the uptime on a graph between these 2 times then I get the following:

You can see above that the "machine running" value is zero for over half of the time. Therefore availability should be much lower (I'd estimate 30-40%).

I think what's happening is the queryTagHistory is just getting the last historically stored tag value. I have set the tag history properties to only record on a tag change ("maximum time between samples" is set to zero.) Therefore, it is calculating the average value as if the endDate was at the last tag change (around 2022-07-15 23:35:00), so it is ignoring the latter part of the graph where the value remains at zero.

Is there a way I can adjust the above script so that the last section of the graph where the value remains at zero is captured?

I believe I could lessen the impact of this by setting a value in "maximum time between samples". However I am trying to avoid this method as I don't want to have unnecessary data points being recorded.

If there is way to manually insert historian data then this might be a viable option as I could trigger an extra historian row to be inserted at the same time as the product is ended.
I thought about manually inserting data into the sqlt_data table but didn't think it was a good idea to mess around with that.

Any help you can provide is very much appreciated.

Many Thanks.

I think the easiest way might be to include a record for the period start and end in your query. Post the query and we’ll see if we can modify it.


Use the </> button to format your code (not the > quote syntax). It preserves indentation, applies syntax highlighting and looks like code.

Hi Transistor,

Sorry, but the query for what? It’s the script I am having trouble with as it is not returning the true average value between the 2 times specified in the queryTagHistory() call.

The startTime and endTime values cannot be changed as these are created when an Operator presses a “Start Product” or an “End Product” button.

Reposting the script code with better formatting:

value = 0

startTime = ‘2022-07-14 19:26:10.260’ #Start time of product.
endTime = ‘2022-07-18 07:05:40.067’ #End time of product.

dataSet = system.tag.queryTagHistory(paths=[‘machine running’], startDate=startTime, endDate=endTime, returnSize=1, aggregationMode=“Average”, returnFormat=‘Wide’)

#Get value from returned dataset.
value = dataSet.getValueAt(0,1)

#Write the average value to the text field.
event.source.text = str(value)

Sorry, I thought you were running a named query. I didn’t read it properly.

I have struggled with the historian aggregation for reasons similar to yours. The operation of the aggregations is rather opaque so I’ve tended to read the raw data and aggregate it myself. Someone else may be able to help.


Tip: you didn’t repost the code, you copied the code from your first post but that had converted the quotes to smart quotes ‘ ... ’ which don’t work when copied into the script console which is expecting ' ... ' regular apostrophes.

Thanks Transistor.

Hopefully someone has had a similar problem and found a way to resolve it through Ignition.

Posting the script again which hopefully is now in the correct format to make it easy to copy / paste into the script console.

value = 0

#Start time of the product
startTime =  '2022-07-14 19:26:10.260'

#End time of the product
endTime =  '2022-07-18 07:05:40.067' 

#Query tag history between the start and end times to get average.
dataSet = system.tag.queryTagHistory(paths=['machine running'], startDate=startTime, endDate=endTime, returnSize=1, aggregationMode="Average", returnFormat='Wide')

#Get value from returned dataset.
value = dataSet.getValueAt(0,1)

#Write the average value to the test field.
event.source.text = str(value)

I don’t use historian much, but there is also the system.tag.queryTagCalulations() function. Be interesting to see what you get if you use the DurationOn / DurationOff aggregates.