I have a similar setup to you so have put together a couple of functions to do what you need, it is perhaps a nasty workaround but it works. May need further error checking, Iām going to start displaying this state pie chart myself so will update if I find any problems.
I have a simple state that is 0 - 3. I record the state number in the historical db. I then have a dataset tag containing the description of each state number.
0 Fault / Stopped
1 Running
2 Discharge High
3 Infeed Low
I have two functions, call the first to get the total minutes for each state over the desired period.
Call the second to substitute the state numbers for the descriptions.
![](https://global.discourse-cdn.com/business4/uploads/inductiveautomation/original/2X/9/961bd7559bcf220682fbcf87778be9ddb3f42fa1.PNG)
![](https://global.discourse-cdn.com/business4/uploads/inductiveautomation/original/2X/7/794a683558cee71ab26285747fd066169882b593.PNG)
def historyduration(tag,startdate,enddate):
ColumnNames = ["t_stamp","code"]
hds = system.tag.queryTagHistory(paths=[tag], startDate=startdate, endDate=enddate, columnNames=ColumnNames, aggregationMode="MinMax", returnFormat='Wide', noInterpolation=True, ignoreBadQuality = 1)
ds = []
dsheader = ['Code','Duration']
#get number of seconds between each event change
for row in range(hds.rowCount):
#first row start time is start of query
if row == 0:
valtime_start = startdate
valtime_end = hds.getValueAt(row,'t_stamp')
valdiff = system.date.secondsBetween(valtime_start, valtime_end)
valcode = hds.getValueAt(row,'code')
#all other rows except last
if (row > 0) and (row < hds.rowCount-1):
valtime_start = hds.getValueAt(row,'t_stamp')
valtime_end = hds.getValueAt(row+1,'t_stamp')
valdiff = system.date.secondsBetween(valtime_start, valtime_end)
valcode = hds.getValueAt(row,'code')
#last row end time is end of query
if row == hds.rowCount - 1:
valtime_start = hds.getValueAt(row,'t_stamp')
valtime_end = enddate
valdiff = system.date.secondsBetween(valtime_start, valtime_end)
valcode = hds.getValueAt(row,'code')
#convert seconds to minutes
valdiff = (valdiff * 1.00) / 60 * 1.00
ds.append([valcode,valdiff])
ds2 = system.dataset.toDataSet(dsheader,ds)
#sort by event code
sds2 = system.dataset.sort(ds2,'Code')
#lastvalue needs a value
lastvalue = -1
duration=0
fds2 = []
#group each like event together and add duration
for row in range(sds2.rowCount):
value = sds2.getValueAt(row,'Code')
thisduration = sds2.getValueAt(row,'Duration')
#if this is a new event, append last event details
if value != lastvalue and lastvalue > -1:
fds2.append([lastvalue,duration])
duration = 0
#if this is the last row add duration and append
if row == sds2.rowCount - 1:
duration = duration + thisduration
fds2.append([value,duration])
duration = duration + thisduration
lastvalue = value
return system.dataset.toDataSet(dsheader,fds2)
def codereplace(ds,descriptions):
#substitute code for description in dataset
nds = []
ndsheader = ['State','Duration']
for row in range(ds.rowCount):
code = ds.getValueAt(row,'Code')
duration = ds.getValueAt(row,'Duration')
for row2 in range(descriptions.rowCount):
if code == descriptions.getValueAt(row2,'Code'):
description = descriptions.getValueAt(row2,'Description')
nds.append([description,duration])
break
return system.dataset.toDataSet(ndsheader,nds)
Hope that helps.