# Trying to get the summed runtime of a historical boolean tag

**URL:** <https://forum.inductiveautomation.com/t/trying-to-get-the-summed-runtime-of-a-historical-boolean-tag/61245>\
**Category:** Ignition\
**Tags:** ignition80, perspective, database\
**Created:** [June 27, 2022, 3:40pm UTC](https://forum.inductiveautomation.com/t/trying-to-get-the-summed-runtime-of-a-historical-boolean-tag/61245 "2022-06-27T15:40:41Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Dean\_Walsh](https://avatars.discourse-cdn.com/v4/letter/d/8797f3/32.png) [@Dean\_Walsh](https://forum.inductiveautomation.com/u/Dean_Walsh)\
**Post date:** [June 27, 2022, 3:40pm UTC](https://forum.inductiveautomation.com/t/trying-to-get-the-summed-runtime-of-a-historical-boolean-tag/61245/1 "2022-06-27T15:40:41Z")

</div>

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)

```auto
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)

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [June 27, 2022, 3:53pm UTC](https://forum.inductiveautomation.com/t/trying-to-get-the-summed-runtime-of-a-historical-boolean-tag/61245/2 "2022-06-27T15:53:04Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Dean\_Walsh](https://avatars.discourse-cdn.com/v4/letter/d/8797f3/32.png) [@Dean\_Walsh](https://forum.inductiveautomation.com/u/Dean_Walsh)\
**Post date:** [June 27, 2022, 3:58pm UTC](https://forum.inductiveautomation.com/t/trying-to-get-the-summed-runtime-of-a-historical-boolean-tag/61245/3 "2022-06-27T15:58:45Z")

</div>

Thank you this is what I’m looking for

---

<div class="post-metadata">

**Author:** ![Kyle\_Latzke](https://avatars.discourse-cdn.com/v4/letter/k/c68b51/32.png) [@Kyle\_Latzke](https://forum.inductiveautomation.com/u/Kyle_Latzke)\
**Post date:** [September 24, 2025, 1:36pm UTC](https://forum.inductiveautomation.com/t/trying-to-get-the-summed-runtime-of-a-historical-boolean-tag/61245/4 "2025-09-24T13:36:15Z")

</div>

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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/a/2ac2d0e08f67db2daec00880a30c1d6e551f13a3.png)

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

```python
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.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [September 24, 2025, 1:49pm UTC](https://forum.inductiveautomation.com/t/trying-to-get-the-summed-runtime-of-a-historical-boolean-tag/61245/5 "2025-09-24T13:49:41Z")

</div>

> [@Kyle\_Latzke](#):
>
> It seems like the value returned is always zero.

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

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [September 24, 2025, 2:42pm UTC](https://forum.inductiveautomation.com/t/trying-to-get-the-summed-runtime-of-a-historical-boolean-tag/61245/6 "2025-09-24T14:42:15Z")

</div>

something like this may help:

```python
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)

```

---

<div class="post-metadata">

**Author:** ![Kyle\_Latzke](https://avatars.discourse-cdn.com/v4/letter/k/c68b51/32.png) [@Kyle\_Latzke](https://forum.inductiveautomation.com/u/Kyle_Latzke)\
**Post date:** [September 24, 2025, 3:15pm UTC](https://forum.inductiveautomation.com/t/trying-to-get-the-summed-runtime-of-a-historical-boolean-tag/61245/7 "2025-09-24T15:15:51Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Kyle\_Latzke](https://avatars.discourse-cdn.com/v4/letter/k/c68b51/32.png) [@Kyle\_Latzke](https://forum.inductiveautomation.com/u/Kyle_Latzke)\
**Post date:** [September 24, 2025, 3:39pm UTC](https://forum.inductiveautomation.com/t/trying-to-get-the-summed-runtime-of-a-historical-boolean-tag/61245/8 "2025-09-24T15:39:55Z")

</div>

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 [~]