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

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

</div>

Put the following into a named query, with value parameters `begin_ts` and `end_ts` of type DateTime:

```auto
SELECT stopCode,
	count(stopCode) as Qty,
	sum(CASE WHEN t_stamp1 > :end_ts THEN :end_ts ELSE t_stamp1 END -
		CASE WHEN t_stamp < :begin_ts THEN :begin_ts ELSE t_stamp END) AS Duration
FROM (
	SELECT t_stamp,
		stopCode,
		coalesce(lead(t_stamp) OVER (ORDER BY t_stamp),
			CASE WHEN current_timestamp < :end_ts THEN current_timestamp ELSE :end_ts END) AS t_stamp1
	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')
) subq
WHERE t_stamp < :end_ts AND t_stamp1 > :begin_ts
GROUP BY stopCode
ORDER BY count(stopCode) desc

```

Provide the precise timestamps for which you wish results (shift, day, week, month) and it will provide the quantity and duration for each code. Any code active at either boundary will be counted in `Qty` but its `Duration` will be clipped to the boundary.

If `endts` is beyond `current_timestamp`, the last entry will be clipped to `current_timestamp`.

---

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