typescript-sqlite-stats-units-zero-inclusion

Answer

Root causes: (1) The DB stores duration_ms, but the API boundary passed raw ms through while consumers formatted the number as seconds → 249053 ms rendered as 249053 s = 69.18h. (2) Aggregates had no status filter and no latency sanity floor, so RUNNING/QUEUED/FAILED rows (0 duration, 0 latency) plus legacy ENDED rows with sub-50ms latency dragged avgLatency to ~4ms.

Fix — one shared filtered aggregate, converted at the repository boundary:

// src/statsRepository.ts — the fixed core (all 4 queries share this WHERE)
const BASE_SELECT = `
  SELECT COUNT(*)          AS count,
         AVG(duration_ms)  AS avg_duration_ms,
         AVG(latency_ms)   AS avg_latency_ms,
         MIN(latency_ms)   AS min_latency_ms,
         MAX(latency_ms)   AS max_latency_ms
  FROM runs
  WHERE status = 'ENDED'          -- filter 1: no in-progress/queued/failed zeros
    AND latency_ms >= 50`;        -- filter 2: sanity floor for legacy 0/sub-50ms rows

// Boundary conversion: the seconds contract (Math.round(ms / 1000))
function toDurationStats(row) {
  return {
    count: row.count,
    avgDurationSeconds: row.avg_duration_ms == null ? 0 : Math.round(row.avg_duration_ms / 1000),
    avgLatencyMs:       row.avg_latency_ms == null ? 0 : Math.round(row.avg_latency_ms),
    minLatencyMs:       row.min_latency_ms ?? 0,
    maxLatencyMs:       row.max_latency_ms ?? 0,
  };
}

// Query path 1/4 — overall
export function getOverallStats(db) {
  return toDurationStats(db.prepare(BASE_SELECT).get());
}
// Query path 2/4 — by day        (appends `, date(started_at/1000,'unixepoch') AS day` + GROUP BY day)
// Query path 3/4 — by hour       (appends `, strftime('%Y-%m-%dT%H:00', ...) AS hour` + GROUP BY hour)
// Query path 4/4 — by tool       (appends `, tool` + GROUP BY tool)

Consumer formatters (the API now returns seconds, so consumers must not re-interpret):

// src/formatters/web.ts — stats cards render ms: seconds -> ms (*1000)
export const webCardDurationMs = (s: number) => Math.round(s * 1000);
export const webCardDurationLabel = (s: number) => `${webCardDurationMs(s).toLocaleString('en-US')} ms`;

// src/formatters/cli.ts — consumes seconds directly, never as ms
export const cliFormatDuration = (s: number) => { /* 10329 -> "2h 52m 9s" */ };
export const cliFormatLatency  = (ms: number) => `${Math.round(ms)} ms`;

Full project: /tmp/stats-fix (src/statsRepository.ts, src/formatters/*.ts, src/fixtures.ts, test/stats.test.ts).