Use max(end)-min(start) as stage timings, not local maximum

dannykopping · dannykopping · commit 68b16ffe0475 · 2024-08-15T16:01:27.000+02:00
Signed-off-by: Danny Kopping &lt;danny@coder.com&gt;
diff --git a/coderd/database/migrations/000245_provisioner_job_timings.up.sql b/coderd/database/migrations/000245_provisioner_job_timings.up.sql
@@ -17,28 +17,28 @@ CREATE TABLE provisioner_job_timings
 );
 
 CREATE VIEW provisioner_job_stats AS
-SELECT pj.id                                                                                  AS job_id,
+SELECT pj.id                                                                                              AS job_id,
        pj.job_status,
        wb.workspace_id,
        pj.worker_id,
        pj.error,
        pj.error_code,
        pj.updated_at,
-       GREATEST(EXTRACT(EPOCH FROM (pj.started_at - pj.created_at)), 0)                       AS queued_secs,
-       GREATEST(EXTRACT(EPOCH FROM (pj.completed_at - pj.started_at)), 0)                     AS completion_secs,
-       GREATEST(EXTRACT(EPOCH FROM (pj.canceled_at - pj.started_at)), 0)                      AS canceled_secs,
-       GREATEST(MAX(CASE
-                        WHEN pjt.stage = 'init'::provisioner_job_timing_stage
-                            THEN EXTRACT(EPOCH FROM (pjt.ended_at - pjt.started_at)) END), 0) AS init_secs,
-       GREATEST(MAX(CASE
-                        WHEN pjt.stage = 'plan'::provisioner_job_timing_stage
-                            THEN EXTRACT(EPOCH FROM (pjt.ended_at - pjt.started_at)) END), 0) AS plan_secs,
-       GREATEST(MAX(CASE
-                        WHEN pjt.stage = 'graph'::provisioner_job_timing_stage
-                            THEN EXTRACT(EPOCH FROM (pjt.ended_at - pjt.started_at)) END), 0) AS graph_secs,
-       GREATEST(MAX(CASE
-                        WHEN pjt.stage = 'apply'::provisioner_job_timing_stage
-                            THEN EXTRACT(EPOCH FROM (pjt.ended_at - pjt.started_at)) END), 0) AS apply_secs
+       GREATEST(EXTRACT(EPOCH FROM (pj.started_at - pj.created_at)), 0)                                   AS queued_secs,
+       GREATEST(EXTRACT(EPOCH FROM (pj.completed_at - pj.started_at)), 0)                                 AS completion_secs,
+       GREATEST(EXTRACT(EPOCH FROM (pj.canceled_at - pj.started_at)), 0)                                  AS canceled_secs,
+       GREATEST(EXTRACT(EPOCH FROM (
+           MAX(CASE WHEN pjt.stage = 'init'::provisioner_job_timing_stage THEN pjt.ended_at END) -
+           MIN(CASE WHEN pjt.stage = 'init'::provisioner_job_timing_stage THEN pjt.started_at END))), 0)  AS init_secs,
+       GREATEST(EXTRACT(EPOCH FROM (
+           MAX(CASE WHEN pjt.stage = 'plan'::provisioner_job_timing_stage THEN pjt.ended_at END) -
+           MIN(CASE WHEN pjt.stage = 'plan'::provisioner_job_timing_stage THEN pjt.started_at END))), 0)  AS plan_secs,
+       GREATEST(EXTRACT(EPOCH FROM (
+           MAX(CASE WHEN pjt.stage = 'graph'::provisioner_job_timing_stage THEN pjt.ended_at END) -
+           MIN(CASE WHEN pjt.stage = 'graph'::provisioner_job_timing_stage THEN pjt.started_at END))), 0) AS graph_secs,
+       GREATEST(EXTRACT(EPOCH FROM (
+           MAX(CASE WHEN pjt.stage = 'apply'::provisioner_job_timing_stage THEN pjt.ended_at END) -
+           MIN(CASE WHEN pjt.stage = 'apply'::provisioner_job_timing_stage THEN pjt.started_at END))), 0) AS apply_secs
 FROM provisioner_jobs pj
          JOIN workspace_builds wb ON wb.job_id = pj.id
          LEFT JOIN provisioner_job_timings pjt ON pjt.job_id = pj.id
