Trying to get the summed runtime of a historical boolean tag

Ignition
, ,
1

Hello, so i have been trying to get a runtime of some boolean tags which are set up with the Historian, when turned on or off they are added to the sql table with a timestamp and the intvalue of 1 or 0,
i would like to somehow display the duration these tags spent as an intvalue of 1 but i have been unsuccessful so far.
I feel like there could be an easier way to do this through ignitions features but not that i ould find so I have tried creating a Named Query as follows. (Let me know if a question like this would be better suited to ask on Stack Overflow)

SELECT :datee as 'datee', :datee2 as 'datee2', d.tagid,
       (Sum(
         CASE 
           -- subtract the timestamps of 'ON' events
           WHEN d.intvalue = 1 THEN -1 * d.t_stamp 
           -- add the timestamps of 'OFF' events
           ELSE d.t_stamp
         END
       ) +
       -- if ended 'ON', so we need to add our endTime parameter
       CASE 
           WHEN d.EndingState = 1 THEN (:datee2/1000)
           ELSE 0 
       END + 
       -- if started 'OFF', so we need to subtract our startTime param
       CASE 
           WHEN d.StartingState = 0 THEN -1*(:datee/1000)
           ELSE 0
       END)
       AS TotalRunTime
-- Sub query to find starting and ending state for the particular time range
FROM (SELECT tagid,
               intvalue,
               t_stamp,
               (SELECT TOP 1 intvalue
                FROM   sqlt_data_1_2022_06
                WHERE  a.tagid = tagid
                       AND t_stamp >= :datee
                       AND t_stamp <= :datee2
                ORDER  BY t_stamp ASC
) AS StartingState,
               (SELECT TOP 1 intvalue
                FROM   sqlt_data_1_2022_06
                WHERE  a.tagid = tagid
                       AND t_stamp >= :datee
                       AND t_stamp <= :datee2
                ORDER  BY t_stamp DESC
) AS EndingState
        FROM   sqlt_data_1_2022_06 AS a
        WHERE a.t_stamp >= :datee
          AND a.t_stamp <= :datee2 and tagid = 4) AS d
GROUP  BY d.tagid, d.EndingState, d.StartingState;

(This query is completely broken it gives crazy answers and i cannot figure i out)

2

There’s a Duration On aggregation mode available wherever you can query the historian.
https://docs.inductiveautomation.com/display/DOC81/system.tag.queryTagCalculations

2 Likes
3

Thank you this is what I’m looking for

4

I’m trying to do this same thing I believe.

I’m having trouble getting queryTagCalculations to work with my Boolean historical data.

My data if just dropped into a table shows all the onchange events
Return Format = Wide, Sample Size = OnChange
Between 7:00 and 7:25 the conveyor ran about 13 minutes.
30 minutes for the whole 7:00-8:00 hour.

image
image455×410 25.4 KB

I wanted to take that time between onchanges 13 minutes total in the table above and show it as On/Running.

rc = event.source.parent
selected_date = rc.getComponent('Date_Sel').date

day_start = system.date.setTime(selected_date, 7, 0, 0)
day_end   = system.date.setTime(system.date.addDays(selected_date, 1), 7, 0, 0)

tagPath = "MainBldg/EastProd/Conveyor/Run Time"

calc = system.tag.queryTagCalculations(
    paths=[tagPath],
    calculations=["DurationOn"],
    startDate=day_start,
    endDate=day_end,
    returnFormat="Wide"
)

headers = ["Date", "Minutes Running", "Percent Running"]
rows = []

if calc.getRowCount() > 0:
    row = system.dataset.toPyDataSet(calc)[0]
    seconds_on = row["DurationOn"]

    if seconds_on is None:
        minutes_on = 0.0
        percent_on = 0.0
    else:
        minutes_on = round(seconds_on / 60.0, 1)
        total_seconds = (day_end.time - day_start.time) / 1000
        percent_on = round((seconds_on / total_seconds) * 100.0, 1)

    rows.append([system.date.format(selected_date, "yyyy-MM-dd"), minutes_on, percent_on])

# Push to table
rc.getComponent("HourlyTable").data = system.dataset.toDataSet(headers, rows)

It seems like the value returned is always zero. If I use that same tag in a table I can see the boolean values.

5

Have you forgotten to loop through calc? It seems to me that you're only checking the first row.

1 Like
6

something like this may help:

runningSeconds = 0

for row, nextRow in zip(calc, calc[1:]):
	if row[1] > 0:
		secondsRunning += system.date.secondsBetween(row[0], nextRow[0])

#Add seconds from last event to endDate, if needed
if nextRow[1] > 0:
	secondsRunning += system.date.secondsBetween(nextRow[0], endDate)
7

I thought when using queryTagCalculations it would go from day_start to day_end and just give one result.

calc = system.tag.queryTagCalculations(
paths=[tagPath],
calculations=["DurationOn"],
startDate=day_start,
endDate=day_end,
returnFormat="Wide"
)

I was trying to do by the hour originally but I couldn’t get any data back other than the t_stamp dates.

1 Like
8

Looks like I needed a [~] at the start of the tag… I thought it was using the tag path stored in SQL which doesn’t have the [~]

1 Like