To calculate time in each equipment state, convert the recorded transitions into intervals, clip those intervals to the reporting window, and sum their durations by state. Include the state already active at the window start and keep unknown time separate. Counting stored rows is not equivalent to measuring elapsed time in an on-change historian.
An automation engineer can use this method to review an integer state tag such as stopped, low speed, high speed or maintenance. The output describes time in recorded states. Turning those states into downtime, availability or another production measure requires a separate agreed definition and schedule.
Retrieve transitions and quality, not a smoothed state curve
In the public multistate duration discussion, a user has collected a fan’s integer states but needs duration per state. The eventual workaround counts regularly sampled values. That can be a useful approximation under stated conditions, but transitions between sample times can be missed or shifted.
For interval reconstruction, request the archived state changes and their status with adequate timestamp precision. Avoid linear interpolation of categorical codes: a value halfway between stopped and maintenance has no necessary operational meaning. Establish whether collection deadbands, aggregation or retention have already removed transitions. A raw retrieval mode cannot restore changes never retained.
Find the last trustworthy state at or before the window start. A preceding record is usable only under a justified state-hold and collection-health policy. A month-old “running” record does not prove the machine ran continuously if collection may have stopped in between. If the initial state cannot be established, classify time before the first valid in-window observation as unknown.
Work a 30-minute window
This synthetic example analyzes [10:00, 10:30) UTC. State 0 means stopped, 1 means low speed, and 2 means high speed. Assume the collector captured all relevant state and quality changes, Good states hold until the next record, and collection health is established. A Bad record starts unknown time until a new Good record arrives; that is the chosen policy for this example.
| Record time | State | Quality | Interval contribution inside the window |
|---|---|---|---|
| 09:58 | 1 | Good | Low speed, 10:00–10:05: 5 min |
| 10:05 | 2 | Good | High speed, 10:05–10:12: 7 min |
| 10:12 | 0 | Bad | Unknown, 10:12–10:15: 3 min |
| 10:15 | 1 | Good | Low speed, 10:15–10:25: 10 min |
| 10:25 | 0 | Good | Stopped, 10:25–10:30: 5 min |
The totals are 15 minutes low speed, 7 minutes high speed, 5 minutes stopped and 3 minutes unknown. They sum to the entire 30-minute window. The Bad record’s numeric zero is not counted as stopped. The 09:58 observation supplies the initial state, but its two minutes before 10:00 do not enter the result.
If a transition occurs exactly at 10:30, it starts outside this window and contributes no duration here. It can establish the starting state of a following window. Adapt the retrieval query to the API’s actual boundary rules rather than assuming it accepts half-open intervals.
Build the interval before grouping by state
For one resolved tag stream, sort transitions by source time and use each record’s next transition as its provisional end. The final record ends at the reporting cutoff, subject to the collection-health policy. Clip every interval with:
clipped_start = max(record_time, window_start)
clipped_end = min(next_transition_or_cutoff, window_end)
Include only positive-duration intervals. Assign their state or unknown category before grouping and summing. Retain the interval output so a reviewer can trace a total back to particular transitions.
In a relational staging table, a window function such as PostgreSQL LEAD can obtain the next timestamp within each ordered tag partition. It is only one part of the method. The query also needs the pre-window state, clipped boundaries, quality policy and final cutoff. Filtering away all pre-window records before finding that state loses the beginning of the period. For TDengine TSDB 3.4.2.0+ batch queries, see LEAD window-function semantics.
Do not manufacture an ordering for conflicting transitions at the same timestamp. If the source provides a meaningful sequence, retain it and apply its semantics. If time precision collapsed distinct transitions or two different states remain unresolved, flag the affected span. A SQL tie-breaker can make output deterministic without making it physically correct.
Keep unknown time in the report
Removing Bad rows before interval construction can accidentally extend the last Good state through a known invalid period. Construct validity intervals first. Likewise, an unexplained gap in collector health should not disappear into a long running interval simply because the value tag is recorded only on change.
The OPC UA quality article discusses why numeric values need their status. Apply the corresponding policy to the actual protocol and historian. If quality or collection health is unavailable, state what cannot be established; do not infer Good status from a returned number.
For a percentage, declare the denominator. In the example, low speed occupies 15/30 = 50% of the full window. It is 15/27 ≈ 55.56% of known-state time. Both are reproducible, but they answer different questions. Report the 3 minutes of unknown time alongside either percentage.
Check native aggregates against the required state meaning
A built-in aggregate may be sufficient for a Boolean running signal. Ignition 8.1’s queryTagCalculations documentation lists DurationOn and DurationOff, together with bounding-value, interpolation and quality options. Verify their exact behavior for the installed provider and configuration. A general on/off aggregate should not be assumed to return a separate duration for each integer state code.
Compare a native calculation with a small manually reconstructed window before using it for many tags. Include a state active before the start, a transition at the end, duplicate same-state records, an invalid interval and a missing initial state. Repeated same-state records should not change the total under the hold model; unresolved missing time should remain visible.
For a single mutually exclusive state stream, accepted state durations plus unknown time should equal the reporting-window duration. Treat a mismatch as an interval or classification defect to investigate. This check does not prove that the field signal is physically correct, but it catches omitted boundaries and double-counted time before the totals reach a report.


