How to calculate Mean Time Between Failures (MTBF)

Formula

MTBF = total operating time ÷ number of failures

SQL query

This example assumes: machine_hours(machine_id bigint, day date, operating_hours numeric), failures(id, machine_id bigint, failed_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 h.machine_id,
       SUM(h.operating_hours) AS operating_hours,
       f.failures,
       SUM(h.operating_hours) / NULLIF(f.failures, 0) AS mtbf_hours
FROM machine_hours h
LEFT JOIN (
  SELECT machine_id, COUNT(*) AS failures
  FROM failures
  WHERE failed_at >= CURRENT_DATE - 180
  GROUP BY machine_id
) f ON f.machine_id = h.machine_id
WHERE h.day >= CURRENT_DATE - 180
GROUP BY h.machine_id, f.failures
ORDER BY mtbf_hours;

Common mistakes that make Mean Time Between Failures (MTBF) wrong

• Operating time excludes time the machine was down or switched off; wall-clock hours overstate MTBF.
• A machine with no failures has an undefined MTBF in the window; widen the window instead of showing zero.
• Mixing machine types in one average hides the fact that they fail at different rates.

Ask for Mean Time Between Failures (MTBF) 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 MTBF for each machine over the last 6 months?
• Which machines failed most often this quarter?