# 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:** 1\
**Showing post:** 71

<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:** [May 10, 2022, 8:42pm UTC](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/71 "2022-05-10T20:42:31Z")

</div>

If I group this query by

```auto
format(dateadd(hh,-8, t_stamp), 'yyyy-MMM-dd') 

```

how do I join it to to another query that will be grouped by

```auto
format(dateadd(hh,-8, t_stamp), 'yyyy-MMM-dd') 

```

both queries have subqueries

still join on mytable from that inner most subquery?  
and if I join, I think my t\_stamps would be messed up?

* * *

it turned out that I could join a subquery on a subquery

```auto
left join ( the entire subquery) sub1
on on format(dateadd(hh,-8, t.t_stamp), 'yyyy-MMM-dd') = sub1.day

```

I apparently need someone to post or I can’t post on this thread because 3 self replies is the limit.  
I think I will have more things to do with this duration query though.

* * *

sept 2022

I am using a union per machine to get more than one machine in the query because my table has all machines in one table.  
I haven’t figured out a way to get the t\_stamps of one machine, then the next, and then the next.  
I think it must be some kind of partition by over combination.

* * *

I think thought these were the same, with isnull being faster.  
Seems to be throwing index errors.

```auto
,isnull(lead(t_stamp) OVER (ORDER BY t_stamp),current_timestamp) as test2

```

```auto
,coalesce(lead(t_stamp) OVER (ORDER BY t_stamp),	CASE WHEN current_timestamp < :endLead THEN current_timestamp ELSE :endLead END) AS lead,t_stamp

```

---

_[View the full topic](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322)._