# Calculating Total Time in Seconds

**URL:** <https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322>\
**Category:** Ignition\
**Created:** [July 28, 2021, 6:44pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322 "2021-07-28T18:44:54Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Brit](https://avatars.discourse-cdn.com/v4/letter/b/c0e974/32.png) [@Brit](https://forum.inductiveautomation.com/u/Brit)\
**Post date:** [July 28, 2021, 6:44pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/1 "2021-07-28T18:44:54Z")

</div>

Hi, I’m working on a project and I’m trying to figure out how to calculate the total time that passes when a boolean tag is active & display that information in a report.

For example, someone activates a toggle button (boolean value = 1) and 30 seconds later, they deactivate the toggle button (boolean value = 0). I want to record & store the time between activation (boolean value = 1) and deactivation (boolean value = 0) and then display that information on a report.

For a better explanation, the picture below shows three sets of time stamps. The toggle button was hit (boolean = 1) and then deactivated (boolean = 0) a few seconds later. I need to figure out how to total the time between the first “1” and the “0”, reset the timer, & then record the next set, and so on.

![Total Time Example](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/1/016bf53cb074ae7dd8d5037132c75aec9c6ab1a0.png)

If anyone has any ideas, I’m all ears!

---

<div class="post-metadata">

**Author:** ![josborn](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@josborn](https://forum.inductiveautomation.com/u/josborn)\
**Post date:** [July 28, 2021, 7:50pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/2 "2021-07-28T19:50:10Z")

</div>

I had to do something similar for a report, where i was logging the time between safety rounds. I logged the tag change events to a mysql database, then created a query data source within the report, and then created a script data source to perform the calculation and write it back to the original data source. This is the part of my code dealing with the time stamp difference.

I don’t know if this can be adapted for your purpose, but it may help.

```auto
secondsBetweenRows = []
	queryData = data['query'].getCoreResults()
	previousTimestamps = {}
	for row in range(queryData.getRowCount()):
		group = queryData.getValueAt(row, 'unit') + queryData.getValueAt(row, 'station') 
		if group in previousTimestamps:
			# do the calculation
			currentTimestamp = queryData.getValueAt(row, 'timeStamp')
			secondsBetween = system.date.secondsBetween(previousTimestamps[group], currentTimestamp)
			secondsBetweenRows.append(secondsBetween)
			previousTimestamps[group] = queryData.getValueAt(row, 'timeStamp')		
		else:
			# first of the group
			secondsBetweenRows.append(0)		
			previousTimestamps[group] = queryData.getValueAt(row, 'timeStamp')
	data['query'] = system.dataset.addColumn(queryData, secondsBetweenRows, 'seconds', int)

```

---

<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:** [July 29, 2021, 12:21pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/3 "2021-07-29T12:21:32Z")

</div>

This may help you out:

```python
# Create sample dataset

sampleHeaders = ['t_stamp', 'value']
sampleData = [['2021-07-01 13:42:28', 1],
              ['2021-07-01 13:43:23', 1],
              ['2021-07-01 13:43:28', 0],
              ['2021-07-01 13:43:29', 0],
              ['2021-07-01 13:43:30', 1],
              ['2021-07-01 13:43:32', 1],
              ['2021-07-01 13:43:36', 0],
              ['2021-07-01 13:43:39', 1],
              ['2021-07-01 13:43:41', 1],
              ['2021-07-01 13:43:45', 0]
             ]
sampleData = [[system.date.parse(row[0]), row[1]] for row in sampleData]

sampleDataSet = system.dataset.toDataSet(sampleHeaders, sampleData)

# -----------------------------------------------------------------------

# Using a PyDataSet will let us use slicing
pyData = system.dataset.toPyDataSet(sampleDataSet)

# Process Data. Since the first row will not have an elapsed time, we can
# use the None for the run time
headers = ['t_stamp', 'value', 'run time']
data = [list(pyData[0]) + [None]]
#initialize elapsed time
elapsedSeconds = 0
# Using zip will let us iterate over two things at once.
# Start currentRow at row 1 instead of row 0
for currentRow, prevRow in zip(pyData[1:], pyData):
	# Check if the value in the previous row is a 1. 
	# If true, add to the elapsed time.	
	if prevRow['value'] == 1:
		elapsedSeconds += system.date.secondsBetween(prevRow['t_stamp'], currentRow['t_stamp'])
	if currentRow['value'] == 0:
		# Check whether we need to add the elapsed time to the 'run time' column or None
		if prevRow['value'] == 1:
			data.append(list(currentRow) + [elapsedSeconds])
			elapsedSeconds = 0
		else:
			data.append(list(currentRow) + [None])
	else:
		data.append(list(currentRow) + [None])
			
datasetOut = system.dataset.toDataSet(headers, data)

```

Resultant dataset:

```nohighlight
row | t_stamp | value | run time
-----------------------------------------------------
0 | Thu Jul 01 13:42:28 EDT 2021 | 1 | None    
1 | Thu Jul 01 13:43:23 EDT 2021 | 1 | None    
2 | Thu Jul 01 13:43:28 EDT 2021 | 0 | 60      
3 | Thu Jul 01 13:43:29 EDT 2021 | 0 | None    
4 | Thu Jul 01 13:43:30 EDT 2021 | 1 | None    
5 | Thu Jul 01 13:43:32 EDT 2021 | 1 | None    
6 | Thu Jul 01 13:43:36 EDT 2021 | 0 | 6       
7 | Thu Jul 01 13:43:39 EDT 2021 | 1 | None    
8 | Thu Jul 01 13:43:41 EDT 2021 | 1 | None    
9 | Thu Jul 01 13:43:45 EDT 2021 | 0 | 6 

```

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [July 29, 2021, 1:04pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/4 "2021-07-29T13:04:54Z")

</div>

I usually do all of this in the database with lead() and/or lag() window functions. Say you have multiple of these recording to a single table, using an id column to segregate them. You record every change, and due to tag restarts or script restarts you get some duplicate rows as you’ve shown. The query to pick out all of the completed cycles that overlap a time period would be something like this:

```auto
Select
	id,
	t_stamp,
	finish_ts,
	finish_ts - t_stamp As duration
From (
	Select
		id,
		t_stamp,
		value,
		lead(t_stamp) Over (Partition By id Order By t_stamp) As finish_ts
	From (
		Select
			id,
			t_tstamp,
			value
		From (
			Select
				t1.id,
				t_stamp,
				value,
				lag(value) Over (Partition By id Order By t_stamp) As priorvalue,
			From myChangesTable t1
			Left Join (
				Select id, max(t_stamp) As lastpriorzerots
				Where t_stamp < :start_ts And value = 0
				Group By id
			) sub1 On t1.id = sub1.id
			Left Join (
				Select id, min(t_stamp) As firstpostzerots
				Where t_stamp >= :end_ts And value = 0
				Group By id
			) sub2 On t1.id = sub2.id
			Where t_stamp >= coalesce(sub1.lastpriorzerots, :start_ts)
				And t_stamp < coalesce(sub2.firstpostzerots, :end_ts)
		) inner1
		Where value = 0 Or coalesce(priorvalue, 0) = 0)
	) outer1
) outer2
Where value = 1
Order By id, t_stamp

```

Note that the above doesn’t clip the runtime to the bounds. When compiling performance stats, you may need to allocate partial values to individual shifts or days or weeks or months.

---

<div class="post-metadata">

**Author:** ![Brit](https://avatars.discourse-cdn.com/v4/letter/b/c0e974/32.png) [@Brit](https://forum.inductiveautomation.com/u/Brit)\
**Post date:** [August 17, 2021, 1:18pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/5 "2021-08-17T13:18:40Z")

</div>

Can this be used with a transaction group?

---

<div class="post-metadata">

**Author:** ![Brit](https://avatars.discourse-cdn.com/v4/letter/b/c0e974/32.png) [@Brit](https://forum.inductiveautomation.com/u/Brit)\
**Post date:** [August 17, 2021, 3:06pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/6 "2021-08-17T15:06:17Z")

</div>

Can this work with a transaction group? And if so, would I write this as a client script? Sorry, fairly new to the ignition world.

---

<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:** [August 18, 2021, 12:02pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/7 "2021-08-18T12:02:53Z")

</div>

> [@Brit](#):
>
> Can this work with a transaction group?

Not sure I understand the question, but my assumption is that the transaction group writes to the database, while the script would read from it. The database would be the common point.

---

<div class="post-metadata">

**Author:** ![Brit](https://avatars.discourse-cdn.com/v4/letter/b/c0e974/32.png) [@Brit](https://forum.inductiveautomation.com/u/Brit)\
**Post date:** [August 18, 2021, 12:55pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/8 "2021-08-18T12:55:56Z")

</div>

I guess my question is, I’m not quite sure where to write the script… I know the transaction group is writing to the database, but not sure where if this should be written as a client script, gateway, etc. If that makes sense…

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [November 22, 2021, 3:34pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/9 "2021-11-22T15:34:25Z")

</div>

Did you figure this out?

I have a similar situation, but many more values rather than 0 and 1.

I think my transaction group writes the code value and t\_stamp to my table.

Then I plan to use a named query to show the data in a table on a perspective view.

What did you end up doing?

---

<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:** [November 22, 2021, 3:38pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/10 "2021-11-22T15:38:53Z")

</div>

I could probably help if you show a sample of the data.

Does this relate to the other thread?

---

<div class="post-metadata">

**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\
**Post date:** [November 22, 2021, 3:40pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/11 "2021-11-22T15:40:10Z")

</div>

This may help you, done by @JordanCClark as well.

> [@Tag history difference over interval](https://forum.inductiveautomation.com/t/tag-history-difference-over-interval/48366):
>
> I am trying to find a better way to do production counts over specific intervals using tag history. Example, I have a tag TestCount, right now it’s a memory tag, but typically this would be an OPC tag from a PLC that increments. I can’t use Count aggregationMode because it may not “catch” each transition, example, it may go from 10 to 13. My desire is to be able to chart this data over a period of time, example, 12 hours, with a certain interval, example 1 hour. So, if at the start of the ho…

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [November 22, 2021, 3:42pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/12 "2021-11-22T15:42:39Z")

</div>

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/9/e93008e05a12f2108cfcdfce79f35a6dbc7dd796.png)

linestops\_ndx, stopCode, t\_stamp

Maybe I should make a new thread instead?  
This is Brit’s thread with the 1 and 0 values

---

<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:** [November 22, 2021, 3:48pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/13 "2021-11-22T15:48:04Z")

</div>

> [@zacharyw.larson](#):
>
> Maybe I should make a new thread instead?

Agree. But, let's ask a moderator to split the topic. Which I guess this counts as asking...

* * *

More questions. I find that I usually have more questions the older I get... 😉

Are you just wanting a count of the individual error codes? You could do that within the query.

---

<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:** [November 22, 2021, 3:49pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/14 "2021-11-22T15:49:28Z")

</div>

To specify, the OP’s data was coming from the historian. Yours, I believe, is coming from a transaction group, correct?

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [November 22, 2021, 4:03pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/15 "2021-11-22T16:03:23Z")

</div>

I think we are both using transaction groups

---

<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:** [November 22, 2021, 4:25pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/16 "2021-11-22T16:25:03Z")

</div>

> [@JordanCClark](#):
>
> Are you just wanting a count of the individual error codes?

You didn't answer, so I going to assume 'yes' 🤪

If you're querying the table generated from the transaction group the query would be something like:

```sql
SELECT stopCode, 
       COUNT(stopCode) as qty
FROM myTable
WHERE t_stamp >= '2021-11-19 16:00:00' 
      and t_stamp < '2021-11-19 18:00:00'
GROUP BY stopCode
ORDER BY COUNT(stopCode) DESC

```

If you are looking to script it, this can be done easily enouh with the _collections.Counter()_ library:

```python
sampleHeaders = ['linestops_ndx', 'stopCode', 't_stamp']
sampleData = [
              [16, 534, '2021-11-19 16:58:22'],
			  [17, 1002, '2021-11-19 16:58:22'],
			  [18, 1001, '2021-11-19 16:58:22'],
			  [19, 1002, '2021-11-19 16:58:22'],
			  [20, 1000, '2021-11-19 16:58:22'],
			  [21, 1002, '2021-11-19 16:58:22'],
			  [22, 534, '2021-11-19 16:58:22'],
			  [23, 1001, '2021-11-19 16:58:22'],
			  [24, 1001, '2021-11-19 16:58:22'],
			  [25, 1002, '2021-11-19 16:58:22'],
			  [26, 634, '2021-11-19 16:58:22']
	         ]

sampleDataSet = system.dataset.toDataSet(sampleHeaders, sampleData)

#-----------------------------------------------------------------------

from collections import Counter

# A query result (using system.db.runPrepQuery(), et al.) comes in as a PyDataSet 
pyData = system.dataset.toPyDataSet(sampleDataSet)

# Get comumn names for the incoming data
pyColNames = list(pyData.getColumnNames())
# Get get values of a column
codeList = pyData.getColumnAsList(pyColNames.index('stopCode'))

headers = ['stopCode', 'qty']

dataOut = Counter(codeList).most_common()

dataSetOut = system.dataset.toDataSet(headers, dataOut)

```

Output dataset:

```plaintext
row | stopCode | qty
--------------------
0 | 1002 | 4  
1 | 1001 | 3  
2 | 534 | 2  
3 | 1000 | 1  
4 | 634 | 1  

```

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [November 22, 2021, 4:32pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/17 "2021-11-22T16:32:35Z")

</div>

Is there a keyword like` Duration(stopCode)` haha because then it would be solved

---

<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:** [November 22, 2021, 4:35pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/18 "2021-11-22T16:35:18Z")

</div>

So, what are you wanting? The time difference between stopCodes?

---

<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:** [November 22, 2021, 4:35pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/19 "2021-11-22T16:35:54Z")

</div>

Nvm. Total time for each stop code?

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [November 22, 2021, 4:51pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/20 "2021-11-22T16:51:02Z")

</div>

Yes.  
Right now I want to know how much time is the machine in each code.  
Then I know how to do get the top x number of those.

* * *

semi unrelated  
count since the 1st of this month

```auto
SELECT stopCode, count(stopCode) as QTY
 FROM myTable
 where (t_stamp> DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 0, 0)) 
               and t_stamp <= GETDATE()
 group by stopCode
 order by count(stopCode) desc

```

[Next page](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322.md?page=2)