# 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:** 3

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

</div>

> [@zacharyw.larson](#):
>
> Column ‘subq.t\_stamp’ is invalid

You aren't doing a sum() or count() or anything else that needs grouping, so ditch the group by clause.

---

<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, 8:00pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/42 "2021-11-22T20:00:14Z")

</div>

ah, so when I do the sum, then I can group?

```auto
select myCode, Sum(Seconds)

from(
select myCode, datediff(second,t_stamp,lead) as seconds

from(
SELECT myTable_ndx , myCode,
		t_stamp,
	lead(t_stamp) over (order by t_stamp) AS lead
	from myTable 	
)subq

)subqtwo

group by myCode
order by sum(seconds) desc

```

ITS ALIVE

I just need to go to a meeting and come back and order by date

omg thanks so much

And probably add the truncating for dates like you had

---

<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 23, 2021, 2:20pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/43 "2021-11-23T14:20:15Z")

</div>

```auto

select stopCode, sum(seconds)

from(
select stopCode, datediff(second,t_stamp,lead) as seconds

FROM (
	SELECT mytable_ndx , stopCode,
		t_stamp,
	lead(t_stamp) over (order by t_stamp) AS lead
	from mytable 
	) subq
where t_stamp >= :beginTS and ( lead < :endLeadTS)
) subqtwo

group by stopCode
order by sum(seconds) desc

```

This is working  
The date parameters seem to working correctly

I am not sure it is very efficient as I could not seem to get the where clause in that primary query

edit  
also, I need to look more at pturmel’s post because his code was capturing accurate conditions at the beginning and ending of the time range.

Can you do a case in the where clause?  
how does current\_timestamp work in that post?

---

<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:** [November 23, 2021, 2:40pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/44 "2021-11-23T14:40:48Z")

</div>

> [@zacharyw.larson](#):
>
> The date parameters seem to working correctly

- They are excluding time spans that cross the boundaries. (You have reversed the comparisons compared to my recommendation.) Your count and time total will be off.
- Using a window function's value in the _inner_ where clause will bog this query down when your table grows large. But the query with the lead() function needs both boundaries on t\_stamp to go fast, which is why I used the innermost queries to determine the bounds.

The layering in my example code was not accidental.

---

<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:** [November 23, 2021, 2:43pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/45 "2021-11-23T14:43:56Z")

</div>

> [@zacharyw.larson](#):
>
> how does current\_timestamp work in that post?

It provides a secondary clip for the situation where `end_ts` is beyond _ **now** _, like might happen if you set the bounds to cover today. The final entry in your table is still "in progress", so its endpoint should be _now_, not midnight tonight.

---

<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 23, 2021, 3:50pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/46 "2021-11-23T15:50:36Z")

</div>

@zacharyw.larson I have gotten into the habit where I test my queries with a large table, say 4-5 million rows, to test efficiency. This will typically reveal areas where I can optimize now, instead of months/years down the road.

Might be useful for you to try as well.

---

<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 24, 2021, 4:32pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/47 "2021-11-24T16:32:00Z")

</div>

```auto
select myCode, sum(datediff(second,
	(CASE WHEN t_stamp< :beginTS THEN :beginTS Else t_stamp END),
	(CASE WHEN lead> :endLead THEN :endLead else lead END)))as seconds

FROM (
	SELECT myCode,
		t_stamp,
	lead(t_stamp) over (order by t_stamp) AS lead
	from myTable 
	) subq
where lead >= :beginTS and ( t_stamp < :endLead )

group by myCode
order by sum(datediff(second,t_stamp,lead)) desc

```

It isn’t throwing an error.  
It looks right.

I think it captures the boundaries precisely at beginTS and endLead which are parameters for the interval the query is to look at.

---

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

</div>

Yes, that should get the right answers. However, with no where clause in the inner query, it (the inner query) will always search the entire table. That will crush you when the table grows to any significant length.

Edit: Hmm. It will also get the wrong answer if there isn’t at least one row in the table after `:endLead`.

---

<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 24, 2021, 4:48pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/49 "2021-11-24T16:48:40Z")

</div>

**I am struggling to figure out how to put**

```auto
where lead >= :beginTS and ( t_stamp < :endLead )

```

inside the inner query

**Might have fixed when there isn’t a row after :endLead**

```auto
(CASE WHEN lead> :endLead or lead is null THEN :endLead else lead END)))
as seconds

```

**removed null codes**

```auto
where lead >= :beginTS and ( t_stamp < :endLead ) and myCode is not null

```

---

<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:** [November 24, 2021, 5:13pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/50 "2021-11-24T17:13:45Z")

</div>

> [@zacharyw.larson](#):
>
> inside the inner query

You don't. You need a different set of comparisons. Look at my example more closely.

---

<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:** [November 24, 2021, 5:14pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/51 "2021-11-24T17:14:58Z")

</div>

> [@zacharyw.larson](#):
>
> removed null codes

It isn't a null in `myCode` that is the problem, it is a null in `lead` when there's no row after.

---

<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 24, 2021, 5:16pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/52 "2021-11-24T17:16:03Z")

</div>

I had problems in both

I am looking at your example lots

It has shown me a lot, and I will keep looking at it as you have recommended.  
Thanks very much for your help.

edit  
well now it is nearly identical to what you had posted  
not sure why it didn’t work when I first tried it

```auto
select stopCode, sum(datediff(second,
	(CASE WHEN t_stamp< :beginTS THEN :beginTS Else t_stamp END),
	(CASE WHEN lead> :endLead or lead is null THEN :endLead else lead END)))
    as seconds
FROM (	SELECT stopCode, t_stamp,
	        coalesce(lead(t_stamp) OVER (ORDER BY t_stamp),
			CASE WHEN current_timestamp < :endLead THEN 
            current_timestamp ELSE :endLead END) AS lead
	from mytable 
	WHERE t_stamp >= coalesce((
		SELECT TOP 1 t_stamp
		FROM mytable
		WHERE t_stamp < :beginTS
		ORDER BY t_stamp DESC), '1900-01-01')
	AND t_stamp <= coalesce((
		SELECT TOP 1 t_stamp
		FROM mytable
		WHERE t_stamp > :endLead
		ORDER BY t_stamp), '2100-01-01') ) subq
	where lead >= :beginTS and ( t_stamp < :endLead ) and stopCode is not null
group by stopCode
order by sum(datediff(second,t_stamp,lead)) desc

```

---

<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:** [November 24, 2021, 5:39pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/53 "2021-11-24T17:39:01Z")

</div>

Let me break this part down for you:

```auto
	WHERE t_stamp >= coalesce((
		SELECT TOP 1 t_stamp
		FROM myTable
		WHERE t_stamp < :begin_ts
		ORDER BY t_stamp DESC), '1900-01-01')
	  AND t_stamp <= coalesce((
		SELECT TOP 1 t_stamp
		FROM myTable
		WHERE t_stamp > :end_ts
		ORDER BY t_stamp), '2100-01-01')

```

For lead() to work as we desire, it always needs **two** rows, the one we are “on”, and the next one. On the last row, `lead` is null because there’s no row after it. Which means, to compute durations at the boundaries of our desired time span, we have to include at least one row _ **outside** _ the time span on either end.  
We can find the last row _before_ our timespan like so:

```auto
SELECT TOP 1 t_stamp FROM myTable WHERE t_stamp < :begin_ts ORDER BY t_stamp DESC

```

We can find the first row _after_ our timespan like so:

```auto
SELECT TOP 1 t_stamp FROM myTable WHERE t_stamp > :end_ts ORDER BY t_stamp

```

We are allowed to use `ORDER BY` in these subqueries because we are using `TOP 1` to get just the first result. However, these two queries can return no row at all, which would be a null. So these are wrapped in the `coalesce()` function, supplying a far past date or a far future date when necessary.

Using these timestamps in the inner query gives that query all the rows in the desired timespan, plus one row just outside the timespan on either end. That gives the `lead()` function something to work with at the boundaries.

---

<div class="post-metadata">

**Author:** ![mcgheeiv](https://avatars.discourse-cdn.com/v4/letter/m/71c47a/32.png) [@mcgheeiv](https://forum.inductiveautomation.com/u/mcgheeiv)\
**Post date:** [November 24, 2021, 8:38pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/54 "2021-11-24T20:38:37Z")

</div>

You might want to consider using a table with two dates, let’s call them date\_beg and date\_end, in addition to all the other data you want with the event. When an event starts insert a record into the table, filling in date\_beg but leaving date\_end null. When the event ends, update the record, putting the end time into date\_end. The query to determine the duration becomes trivial at this point.

---

<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:** [November 24, 2021, 8:48pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/55 "2021-11-24T20:48:34Z")

</div>

> [@mcgheeiv](#):
>
> When the event ends

These events don't end. Your approach would require recording each timestamp in two places. Not good. Competent databases can do intervals with lead() or lag() with essentially zero cost, guaranteeing consistent results.

---

<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:** [December 2, 2021, 7:04pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/56 "2021-12-02T19:04:24Z")

</div>

> [@Organizing SQL tables and named queries efficiently](https://forum.inductiveautomation.com/t/organizing-sql-tables-and-named-queries-efficiently/53212/13):
>
> Event logs are the poster child for “tall” tables. So, option 1, unquestionably. For efficiency, I would use a numeric foreign key for machine, where a separate, relatively static table of machines has that as a primary key. I would also omit duration, as it is easily generated from lead(t\_stamp) over (partition by machine order by t\_stamp).

When you said easily generated, did you mean there is an easier way to get the time between timestamps and sum them up?

Or did you mean a query similar to discussed here used with  
` lead(t_stamp) over (partition by machine order by t_stamp)`  
to omit other machines if the table has many machines

---

<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:** [December 2, 2021, 7:30pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/57 "2021-12-02T19:30:30Z")

</div>

I meant using lead(). If you are using a query to get multiple machine’s results at once, you would use `partition by machine` in the OVER clause so that lead() looks for the next timestamp for the _matching machine_. If you use a WHERE clause in the query to only return results for a single machine, you can omit that part of the over clause.

I didn’t mean “easy” from _ **your** _ point of view, but from the _ **database** _ point of view.

---

<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:** [December 3, 2021, 6:28pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/58 "2021-12-03T18:28:49Z")

</div>

```python
select myCode as Fault_Code,count(myCode) as Stop_Occurences, 
       sum(datediff(second,
	(CASE WHEN t_stamp< :beginTS THEN :beginTS Else t_stamp END),
	(CASE WHEN lead> :endLead or lead is null THEN :endLead else lead END)))
             as Total_Seconds
FROM ( SELECT myCode, t_stamp,
	coalesce(lead(t_stamp) OVER (ORDER BY t_stamp),
			CASE WHEN current_timestamp < :endLead THEN current_timestamp
                 ELSE :endLead END) AS lead
	from myTable 
	WHERE t_stamp >= coalesce((
		SELECT TOP 1 t_stamp
		FROM myTable
		WHERE t_stamp < :beginTS
		ORDER BY t_stamp DESC), '1900-01-01')
	AND t_stamp <= coalesce((
		SELECT TOP 1 t_stamp
		FROM myTable
		WHERE t_stamp > :endLead
		ORDER BY t_stamp), '2100-01-01')	) subq
	where lead >= :beginTS and ( t_stamp < :endLead ) and myCode is not null 
group by myCode
order by sum(datediff(second,t_stamp,lead)) desc

```

sample results  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/d/2d745966f1a84859b0dba81ff25faef9cf4dd7b7.png)

How do I append the table to show the reason from a dictionary.  
I have a dictionary with all the code matched to text for the reason.  
For example in project library I have a script dictionary:

```python
machineCode_Map ={
    42 : "We lost the towel"
    66: "The drones betrayed us"
}
def get_machineCode(v):
    return machineCode_Map[v]

```

## How can I append to the table to show the reason text next to the codes?

I know one way is for me to make a table of my dictionary, then join.  
I have many machines though, and I was going to make this work for multiple machines.  
I have made a transaction group already with Machine\_ID columns.

Therefore, I am hoping on the perspective table component I can somehow attached the dictionary values based on the code in that row. Maybe this is unlikely.

---

<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:** [December 3, 2021, 7:06pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/59 "2021-12-03T19:06:29Z")

</div>

> [@zacharyw.larson](#):
>
> I know one way is for me to make a table of my dictionary, then join.

That's what I would do.

---

<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:** [December 3, 2021, 7:11pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/60 "2021-12-03T19:11:43Z")

</div>

What if you had 60 machines with unique dictionaries?

* * *

```auto
where lead >= :beginTS and ( t_stamp < :endLead ) and Code is not null and ( :machine=-1 )

```

I got this working to show me all the machines codes.

I was thinking like

```auto
where lead >= :beginTS and ( t_stamp < :endLead ) and Code is not null 
and ( :machine=-1 or Line=Case when :machine=1 then 'blueMachine' else null end)

```

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

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