Three fault records within an hour do not necessarily represent three separate outages. Reports and conversational analytics need the duration of the set of times when equipment was stopped, rather than the sum of event durations. We assume the inputs represent trustworthy downtime intervals and derive constructed examples using PostgreSQL 18 documentation; no production performance experiment is claimed.
Fix the equipment, reporting window and meaning of downtime
Define the metric fully: for a specified device, measure elapsed time within specified planned production periods during which at least one qualifying downtime event is active. An alarm, maintenance ticket or communications outage does not necessarily establish equipment downtime. If the source contains isolated state observations, first construct intervals using an explicit validity limit and unknown-state policy. This article does not assume the state between observations is automatically known.
Reliable enterprise Text-to-SQL starts with metric meaning before query generation. Freeze the device identifier, start and end fields, reporting timezone, timestamp precision, production schedule, exclusions and data cutoff. Summing across devices yields device-minutes. Wall-clock time when a production line is fully stopped is a different metric requiring a definition of which combinations of device states stop the line.
Use half-open intervals [start,end), including the start and excluding the end. Adjacent shifts can then meet at a boundary without duplicate attribution. PostgreSQL supplies tstzrange for timestamp-with-time-zone ranges and multiranges for multiple separated intervals.[1] A type expresses boundaries; it cannot decide whether an alarm is a downtime event.
A nested example requires the maximum of all earlier ends
Construct three events for one device on one day in one timezone: A runs 08:00–09:00, B 08:10–08:20, and C 08:30–09:10. Their durations sum to 60+10+40=110 minutes. Their union spans 08:00–09:10, covering 70 minutes. B is wholly inside A and adds no downtime.
Sorting by start and using only LAG(end) incorrectly compares C's 08:30 start with B's 08:20 end, creating a new group although A remains active until 09:00. Compare against the maximum end among all preceding records. PostgreSQL's LAG returns a value from a preceding row; it does not calculate this maximum.[4]
Call that preceding maximum prior_end. Start a group for the first row, then start another only when start exceeds prior_end. Cumulatively sum group flags to obtain group_id, and take each group's earliest start and latest end. For duration calculation we merge adjacent intervals when start equals prior_end. Preserve event identity separately if fault counts matter: the number of merged segments is not the number of incidents.
Clip to reporting boundaries and make intervals finite
For a window [W0,W1), clip each valid event to s=max(start,W0) and e=min(end,W1), retaining only s<e. In a constructed 08:15–09:00 window, A contributes a 45-minute clipped interval, B five minutes and C thirty. Their sum is 80, but their union is only 45. Checking whether a total exceeds shift length is insufficient; some double-counted totals still fall within that bound.
Missing starts, reversed endpoints, unresolvable timezones and conflicting revisions belong in a quality-exception process. A missing end may mean an ongoing outage or an incomplete record. Substitute a frozen data cutoff T only when the source explicitly marks the event as active, and never extend beyond the reporting window. Repeated queries of the same snapshot must use the same T rather than the changing server clock.
If T precedes W1, the remaining period is not observed normal operation. Label the result provisional through T and disclose the incomplete planned window. Zero-length events add no duration but may remain for event-count auditing. Clock corrections, late closure and manual revocation require versions; preserve revision reasons and affected windows when recalculating old reports.
When a shift has several planned production segments, first construct a non-overlapping planned-time set, then intersect it with downtime. Whether lunch is excluded belongs in the metric contract, not an arbitrary subtraction from endpoints. Across daylight-saving transitions, subtract resolved instants rather than assuming every local day has twenty-four hours.
Two implementation paths: window grouping or multirange aggregation
After cleaning and clipping, partition by device and reporting window and order stably by s, e and event_id. Use event_id to resolve ties, with deterministic ordering guaranteed by the cleaning layer. Compute MAX(e) OVER (PARTITION BY device_id, window_id ORDER BY s, e, event_id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) as prior_end. Explicitly exclude the current row; its own end would otherwise conceal new-group boundaries.
In a subsequent query layer, generate new_group with CASE WHEN prior_end IS NULL OR s > prior_end THEN 1 ELSE 0 END. In another layer, cumulatively sum new_group using the same order and ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Group by device, window and group number, taking MIN(s) and MAX(e), then sum the lengths of those disjoint segments. Separate layers avoid nested window calculations and expose intermediate results for inspection.
PostgreSQL 18 offers another path: construct tstzrange(s,e,'[)') for each segment, aggregate with range_agg by device and window, expand the multirange with unnest, and sum upper(r)-lower(r) for the resulting ranges.[2][3] Validate finite boundaries first. This computes a union; range_intersect_agg computes the common intersection of all inputs and is not a substitute.
Nor should a multirange's earliest start and latest end be treated as its total duration. Intervals 08:00–08:10 and 08:20–08:30 cover twenty minutes, while their enclosing span covers thirty. range_merge returns the smallest enclosing range and includes that gap.[3] Preserve the multirange and expand its components to retain intervening operation.
Window grouping is portable in concept and exposes intermediate state, but requires checking the target SQL dialect. Multiranges are concise but depend on database support. For large inputs, inspect skew by device, sorting resources, window filtering and execution plans. Selecting only events whose start lies within the shift loses earlier outages continuing into it; filter for interval intersection instead.
Cause contributions overlap, and no rows do not establish zero downtime
If A, B and C have different causes, separate cause coverage is 60, 10 and 40 minutes, totaling 110 against seventy device minutes. Those are overlapping views, not mutually exclusive parts of a total. Mark them non-additive. A pie chart or shares summing to 100% requires an additional attribution policy.
One approach splits time at every endpoint into atomic segments, identifies the active cause set in each, and applies approved primary-cause priorities or allocation rules. Equal splitting is an accounting convention, not proof of equal physical causation. Retain event-to-segment mappings, policy versions and allocations, so allocated durations reconcile with total device downtime while preserving the overlapping evidence.
No qualifying rows can mean normal operation or missing collection. Many PostgreSQL aggregates return NULL on empty input.[2] Displaying zero requires independent completeness evidence such as collection heartbeats, state coverage and completed source batches. An unconditional final COALESCE must not silently turn absence of evidence into evidence of no downtime.
Accept the result with independent checks and invariants
A minimum counterexample set includes nested, partially overlapping, duplicate, adjacent, separated, cross-shift, active unclosed and reversed intervals, plus simultaneous outages on two devices. The three-event example has a hand-derived expectation of seventy minutes, or forty-five after clipping. The two separated segments total twenty. These are semantic derivations; deployment teams must execute and retain results on their actual engine and field types.
For small samples, use an independent endpoint sweep: sort all endpoints, count active events between successive endpoints and add a segment's length only when the count is positive. Process simultaneous increments and decrements together to avoid boundary-order artifacts. This differs from the running-maximum grouping implementation and helps uncover nesting mistakes. Fixed one-minute sampling introduces rounding and should not be used as an exact reference.
Check invariants: duplicating an event or reordering inputs must not change union length; adding a wholly contained event must not increase it; adding a valid downtime segment must not reduce coverage; device coverage must not exceed its effective reporting window. Splitting a window into disjoint subwindows and recombining their results must agree with the whole. Separately test device isolation, allocation conservation and repeatability for the same snapshot.
Deliver more than a minute count: include device and window, production-schedule version, data cutoff, valid and exceptional record counts, merged segments, source-event references and calculation version. Establish a reconcilable time set before letting a report or conversational system explain it. Successful query execution is an execution check, not a substitute for business acceptance.