A ranking of delay hours is useful only if the events represent comparable time. Duplicate messages, unfinished events and overlapping states can produce a convincing Pareto chart that counts the same minute twice.

This note describes an analytical method, not an employer implementation. Every identifier and value below is synthetic. It uses no operational database names, proprietary queries or real production values.

Define the question and the clock

Start with a decision: which delay categories deserve investigation for a defined equipment group and reporting period? Separate that question from utilization, availability and productivity. Those indicators need their own approved definitions and denominators.

Use half-open intervals: the start belongs to an event and the end belongs to the following interval. Define the reporting time zone, including how a shift crossing midnight is assigned. Preserve event timestamps with their source zone, and normalize instants before comparing them. Arrival time is useful for freshness checks; it is not a substitute for event time.

Record the extract cutoff. An open event cannot silently acquire the current time on every rerun. Either cap it at that recorded cutoff and flag it provisional, or exclude it and report the uncovered duration.

Reconcile events before calculating hours

Preserve an immutable raw extract and build a separate prepared table. Keep the original event identifier, source revision, equipment mapping, raw category and rule version. A corrected category should remain traceable to the original event.

Check at least these cases:

  • Duplicate identifiers and late revisions: resolve with a documented version rule, rather than deleting arbitrary rows.
  • End before start: quarantine the event; an absolute value would hide the defect.
  • Overlapping intervals for one equipment item: investigate the state model before summing durations.
  • Missing categories: retain an explicit unmapped group rather than discarding its hours.
  • Missing coverage: report gaps separately; absence of events does not prove productive time.

Overlaps may be legitimate when different state dimensions coexist. A maintenance flag and an operating state should not be combined into a single additive duration without a precedence rule approved by the operational owner.

Clip events to the reporting window

The following standalone PostgreSQL example demonstrates clipping one already-reconciled interval. It does not resolve overlap or establish a production schema.

WITH events(asset_id, start_at, end_at) AS (
  VALUES ('example-hauler',
    TIMESTAMPTZ '2026-10-01 05:50:00+00',
    TIMESTAMPTZ '2026-10-01 06:20:00+00')
), reporting_window AS (
  SELECT TIMESTAMPTZ '2026-10-01 06:00:00+00' AS lo,
         TIMESTAMPTZ '2026-10-01 07:00:00+00' AS hi
)
SELECT asset_id,
  EXTRACT(EPOCH FROM (
    LEAST(end_at, hi) - GREATEST(start_at, lo)
  )) / 60 AS minutes_in_window
FROM events CROSS JOIN reporting_window
WHERE end_at > start_at
  AND start_at < hi AND end_at > lo;

The illustrative result is 20 minutes. Use explicit offsets rather than relying on a session's default time zone. PostgreSQL documents timestamp subtraction and EXTRACT in its date/time functions reference.

Use Python to investigate, not conceal exceptions

Python can compare category distributions, plot event timelines and inspect outliers after the SQL preparation. Retain invalid input in a separate diagnostic dataset. For a small illustrative timestamp check:

import pandas as pd
 
raw = pd.Series(["2026-10-01T06:00:00Z", "invalid"])
parsed = pd.to_datetime(raw, utc=True, errors="coerce")
invalid = raw.notna() & parsed.isna()
print({"invalid_timestamp_count": int(invalid.sum())})

Here errors="coerce" makes invalid timestamps visible as NaT; it does not make them acceptable. The synthetic check reports one invalid timestamp. Keep the rejected strings and report their count alongside accepted coverage. See the pandas conversion reference.

Publish a defensible comparison

Deliver category hours together with equipment coverage, excluded intervals, unmapped hours, extract cutoff and transformation version. Compare like equipment and equivalent exposure; a unit with more observed hours can have more delay hours without a worse delay rate.

Review the largest contributors with operations before assigning causes. Event categories describe recorded states, while root cause requires additional evidence. If identity, time or coverage cannot be defended, improve the dataset before interpreting the chart.

For the broader integration boundary, see Mine-to-Mill data architecture. For reusable quality policies, see historian data quality rules.