Ignition tag historian - cumulative time spent at value

Ignition
1

Ignition 8.1, MSSQL.

Hi.

I am using Ignition to monitor the operating status and collect downtime codes from several CNC machines. These integer values are logged by the ignition tag historian on value change. The goal is to calculate OEE and determine the most frequent causes of downtime.

For the operating status, I have written a script that loads a snapshot of data from the historian. This is passed through a case select (multiple ifs) which increment a timer for each value of interest. The end result is a summary dataset with the total number of seconds spent in each state (within the time window). This is converted to % in another function. This works OK when there are only a handful of values to scan for. Now I must repeat this for the downtime codes. There are >10 codes and these are also stored in a SQL table. Is there a less clunky solution using inbuilt functions or the SQL server itself?

Some of my code for the CNC machine operating status analysis is below.

def returnStateSeconds(sqlData):
	
	# Iterate through the dataset and increment timers for each state.
	
	# Declare variables
	cutting = float(0)
	idle = float(0)
	error = float(0)
	no_data = float(0)
	
	# convert to pydataset
	pyData = system.dataset.toPyDataSet(sqlData)			
		
	for i in range(len(pyData) - 1): 
		if pyData[i][1] == 0:
			no_data += system.date.secondsBetween(pyData[i][0], pyData[i+1][0])
		elif pyData[i][1] == 1:
			idle += system.date.secondsBetween(pyData[i][0], pyData[i+1][0])
		# Note. State codes 2 (In process) and 3 (OP30) are grouped together into "Cutting".
		elif pyData[i][1] == 2 or pyData[i][1] == 3:
			cutting += system.date.secondsBetween(pyData[i][0], pyData[i+1][0])
		elif pyData[i][1] == 4:
			error += system.date.secondsBetween(pyData[i][0], pyData[i+1][0])

	utilData = []															# Create an empty list
	headers = ["state", "seconds"]											# Set the dataset headers
	states = ["Cutting", "Idle", "Error", "No data"]						# List of states
	seconds = [cutting, idle, error, no_data]								# List of data
	
	for j in range(len(states)):											# Add the rows to the list
		utilData.append([states[j],seconds[j]])
	
	return system.dataset.toDataSet(headers,utilData)					# Return the data as a normal dataset

Similar questions have been raised in these posts without a full solution.
How to get the total time of an active tag for every hour? - Ignition - Inductive Automation Forum
Historical tags - Ignition - Inductive Automation Forum

Any help would be great.

Thanks!

2

The use of a dictionary can be useful:

def returnStateSeconds(sqlData):
	# Iterate through the dataset and increment timers for each state.
	
	# Declare state dict
	stateDict = { 
	               0 : {'state':'No data'}
	               1 : {'state':'Idle'}
	               2 : {'state':'Cutting'}
	               4 : {'state':'Error'}
	             }

	# Initialize zero values:
		for key in stateDict.keys():
			stateDict[key]['value'] = 0.0
	
	pyData = system.dataset.toPyDataSet(sqlData)			
		
	# Process rows
	for row, nextRow in zip(pyData, pyData[1:]): 
		stateDict[row['value']] =+ system.date.secondsBetween(row['t_stamp'], nextRow['t_stamp'])

	# create output dataset
	headers = ["state", "seconds"]
	data = [[stateDict[key]['state'], stateDict[key]['value']] for key in sorted(stateDict.keys())]
	return system.dataset.toDataSet(headers, data)

If you need the states in a certain order, you can use an ordered dictionary.

def returnStateSeconds(sqlData):
	from collections import OrderedDict
	# Iterate through the dataset and increment timers for each state.
	
	# Declare states as an ordered dictionary
	stateDict = OrderedDict({ 
	                          2 : {'state':'Cutting'}
	                          1 : {'state':'Idle'}
	                          4 : {'state':'Error'},
							  0 : {'state':'No data'}
	                       })

	# Initialize zero values:
		for key in stateDict.keys():
			stateDict[key]['value'] = 0.0
	
	pyData = system.dataset.toPyDataSet(sqlData)			
		
	# Process rows
	for row, nextRow in zip(pyData, pyData[1:]): 
		stateDict[row['value']] =+ system.date.secondsBetween(row['t_stamp'], nextRow['t_stamp'])

	# create output dataset
	headers = ["state", "seconds"]
	data = [[stateDict[key]['state'], stateDict[key]['value']] for key in stateDict.keys()]
	return system.dataset.toDataSet(headers, data)

EDIT: If this is just going to be for a downtime pareto, as I’ve read your post more carefully, then you can use system.dataset.sort() before returning.