Querying Historian for CountOn in a Moving Window

Ignition
1

I'm trying to write some stats queries on devices, such as motor running hours, valve strokes etc.

One query that I'm wondering about is getting the number of motor starts in an hour, but that hour window does not necessarily align to the clock. E.g. were there instances were a motor started more than 6 times in a given 60 minute window?

The CountOn aggregation on an hour interval would give me the number of starts, but would miss these trains if they occurred about the hour mark.

All I can think of currently is to retrieve all the On events, and look for 6 more at timestamp+60 minutes. This seems computationally expensive.

Is there a more elegant approach?

2

Not in the historian, no. I would tackle this with ordinary SQL tables for events, and use a LAG(t_stamp) OVER (PARTITION BY id ORDER BY t_stamp ROWS 5 PRECEDING) to extract and compare against an earlier timestamp. The where clause would then include the current row when the delta between the current row's timestamp and the lagged timestamp is <= 60 minutes.

3

I'm using Canary historian, so the SQL querying won't work.

I might just query all the state on events, and then look if the event 5 positions forward is within 1 hour.