Calculating Total Time in Seconds

Ignition
28

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

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.

3 Likes