fix rollup lookback condition

mafredri · mafredri · commit 8b96a5969d44 · 2024-03-22T10:58:45.000Z
diff --git a/coderd/database/dbmem/dbmem.go b/coderd/database/dbmem/dbmem.go
@@ -7963,23 +7963,37 @@ func (q *FakeQuerier) UpsertTemplateUsageStats(ctx context.Context) error {
 	/*
 		latest_start AS (
 			SELECT
-				COALESCE(
-					MAX(start_time) - '1 hour'::interval,
-					-- TODO(mafredri): Fix this, required for tests to pass.
-					NOW() - '2 years'::interval
-				) AS t
+				-- Truncate to hour so that we always look at even ranges of data.
+				date_trunc('hour', COALESCE(
+					MAX(start_time) - '1 hour'::interval),
+					-- Fallback when there are no template usage stats yet.
+					-- App stats can exist before this, but not agent stats,
+					-- limit the lookback to avoid inconsistency.
+					(SELECT MIN(created_at) FROM workspace_agent_stats)
+				)) AS t
 			FROM
 				template_usage_stats
 		),
 	*/
 
 	now := time.Now()
-	latestStart := now.AddDate(-2, 0, 0)
+	latestStart := time.Time{}
 	for _, stat := range q.templateUsageStats {
 		if stat.StartTime.After(latestStart) {
 			latestStart = stat.StartTime.Add(-time.Hour)
 		}
 	}
+	if latestStart.IsZero() {
+		for _, stat := range q.workspaceAgentStats {
+			if latestStart.IsZero() || stat.CreatedAt.Before(latestStart) {
+				latestStart = stat.CreatedAt
+			}
+		}
+	}
+	if latestStart.IsZero() {
+		return nil
+	}
+	latestStart = latestStart.Truncate(time.Hour)
 
 	/*
 		workspace_app_stat_buckets AS (
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/insights.sql b/coderd/database/queries/insights.sql
@@ -347,10 +347,14 @@ WHERE
 WITH
 	latest_start AS (
 		SELECT
-			COALESCE(
+			-- Truncate to hour so that we always look at even ranges of data.
+			date_trunc('hour', COALESCE(
 				MAX(start_time) - '1 hour'::interval,
-				NOW() - '6 months'::interval
-			) AS t
+				-- Fallback when there are no template usage stats yet.
+				-- App stats can exist before this, but not agent stats,
+				-- limit the lookback to avoid inconsistency.
+				(SELECT MIN(created_at) FROM workspace_agent_stats)
+			)) AS t
 		FROM
 			template_usage_stats
 	),
