Hello all
I am working on a data aggregation system that is to read the tag history and show trends on the data based on the day/week/month/year. It has currently run for 24 hours and it is already starting to slow due to the for loop addition of values
I am wondering if it is possible to have an sql query do the work as they tend to go quicker through the math than a for loop, and aggregate as needed.
If it is possible, can either example code or a good explanation be given as well?
Thank you
Show us your query and your code. (Use the </> button to format your code properly.)
Can you confirm that you are using the Ignition historian to save the history or have you created your own historian db / table?
I am using the tag historian to save the data into a property variable.
I am using the historical date range on it, with the start and end date bound to a date range component
This data is used directly in a line graph
It is also referenced in the following code (I cannot post from the computer the code is on, so this is rewritten here)
import datetime
import time
data = self.historianData
#create dataset
tempDataHeaders = ["t_stamp", "total"]
tempDataData = []
barDuration = self.getComponent('Dropdown').selectedLabel
#for each row in data, get date component
for i in range(data.getRowCount()):
dateStamp = data.getValueAt(i, 't_stamp')
total - data.getValueAt(i, 'total')
#convert total based on selected units
if self.getComponent('CheckBox').selected:
total = total / 3.6 #1MWs is 1/3.6 kwh
#create date that matches each of the dropdown capabilites
year = system.date.getYear(datestamp)
#set date to first day of year
dateYear = system.date.getDate(year, 0, 1)
month = system.date.getMonth(datestamp)
#set date to first day of month
dateMonth = system.date.getDate(year, month, 1)
day = system.date.getDayOfMonth(dateStamp)
#set date to day
dateDay = system.date.getDate(year, month, day)
#get week of year using datetime library
#month is +1 due to datetime being index 1 while ignition is index 0 for Jan
#%U is for sunday as first day of week, Days before first sunday in year are week 0
weekNum - datetime.date(year, month+1, day).strftime("%U")
#get date of the sunday, Y is year 4 digits, U is week number with sunday as start, %w is day of week, sunday as 0
dateweek = time.asctime(time.strptime(str(year) + " " + str(weekNum) + " 0", "%Y %U %w"))
#set date to check against
checkDate = ""
if barDuration == "Years":
checkDate = dateYear
elif barDuration == "Months":
checkDate = dateMonth
elif barDuration == "Weeks":
checkDate = dateWeek
else:
checkDate = dateDay
#find check date index if it already exists
barIndex = -1
if len(tempDataData) != 0:
for i in range(len(tempDataData)):
#column 0 is timestamp
rowDate = tempDataData[i][0]
if rowDate == checkDate:
barIndex = i
#if checkDate index does not exist, add it
if barIndex == -1:
tepDataData.append([checkDate, total])
#else sum over it
else:
#column 1 is total
tempDataData[barIndex][1] += total
#compile dataset
tempData = system.dataset.toDataSet(tempDataHeaders, tempDataData)
#set dataset to bar data
self.barData = tempData
this barData is then used to populate a bar chart.
While writing this, the amount of data logged in the historian is now so large that it maxes out ignition memory, causing ignition gateway to switch to backup temporarily as it resets itself.
I am pretty sure a query can do exactly what I am doing in a fraction of the time without maxing memory, I just don’t know where or how to scoop data out of it, if it is even possible
Looking into more depth of the tag history binding, this is done automatically if aggregation mode is set to time weighted average, sample size interval, and the interval param set to the number of hours making up the drop down, I am testing this, and will reply with results shortly
The tag historian binding does work for what I am trying to do.