How to calculate Mean Time to Repair (MTTR)

Formula

MTTR = total repair time ÷ number of repairs

SQL query

This example assumes: failures(id, machine_id bigint, failed_at timestamptz, restored_at timestamptz). It is written in PostgreSQL syntax; date functions differ slightly in MySQL, BigQuery and SQLite, and your column names will differ from these.

SELECT machine_id,
       COUNT(*) AS repairs,
       AVG(EXTRACT(EPOCH FROM (restored_at - failed_at)) / 3600) AS mttr_hours,
       PERCENTILE_CONT(0.5) WITHIN GROUP (
         ORDER BY EXTRACT(EPOCH FROM (restored_at - failed_at)) / 3600) AS median_hours
FROM failures
WHERE restored_at IS NOT NULL
GROUP BY machine_id
ORDER BY mttr_hours DESC;

Common mistakes that make Mean Time to Repair (MTTR) wrong

• Failures still open have no restore time and are left out, which flatters the average.
• Decide whether waiting for a spare part counts as repair time; it usually does in operational reporting.
• One very long outage can dominate the mean; show the median beside it.

Ask for Mean Time to Repair (MTTR) in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. It does not replace your judgement about the definition: check that the query matches the formula your business uses.

• What is the mean time to repair for each machine?
• Which failures took longer than 24 hours to fix?