Calculating Total Time in Seconds

Ignition
4

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:

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.

6 Likes