Query tag historian

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
        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
        #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.