Double date filter in historical data

Ignition
1

hello, I have a tag which have historical activated… I want to obtain the average value in a range of days (like from 2021-08-01 to 2021-08-30) and in a range of hours (those days but only from 7:00 am to 8 am)

more exactly the tag is an OEE calculation by hour from a machine… this tag change every minute depending of the production of the machine itself… and when the hour change the tag got a reset and star the calculation again…

so i need the “Last value” from the hours, and then get the average of days…

day 1 from 7-8hrs 57.8
day 2 from 7-8hrs 65.2
…
last day from 7-8hrs 87.4

the average value of the OEE of the machine from 7-8hrs during the month was: 70.1

What I will try is to use the system.tag.queryTagHistory in a python for to obtain the last value of every hour (Periodic 1 hour) of the first day and storing it in a data set row and then add 1 day to the filter of time and do it again until the starday were the endday, and then somehow calculate the average in python of the dataset…

but I don’t know if there is another easiest way to do it…

2

hello again, I just want to post my possible solution to this just in case someone had similar problems…

#endDate = "2021-08-29 06:59:59"
endDate = system.date.getDate(2021, 7, 29)
endDate = system.date.setTime(endDate, 6, 59, 59)
print endDate
#endTime = "2021-08-02 06:59:59"
endTime = system.date.getDate(2021, 7, 2)
endTime = system.date.setTime(endTime, 6, 59, 59)
print endTime
#startDate = "2021-08-01 07:00:00"
startDate = system.date.getDate(2021, 7, 1)
startDate = system.date.setTime(startDate, 7, 00, 00)
print startDate

ds1 = []
# Create the pandas DataFrame
headers = ["7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "0", "1", "2", "3", "4", "5", "6"]
ds2 = system.dataset.toDataSet(headers, ds1)

while endTime < endDate :
	#row=0
	dataSet = system.tag.queryTagHistory(paths=['MyTagPath'], startDate=startDate, endDate=endTime, returnSize=24,aggregationMode="LastValue", returnFormat='Wide')
	columnData = []
	for i in range(dataSet.getRowCount()):
		valor = dataSet.getValueAt(i,1)
		columnData.append(valor)
	startDate = system.date.addDays(startDate,1)
	endTime = system.date.addDays(endTime,1)
	ds2 = system.dataset.addRow(ds2, columnData)
print ds2

the last thing I want is to obtain the mean of every column in the final dataset, but I’m searching how to do it…