fix: delete workspace agent stats after 180 days

ethanndickson · ethanndickson · commit 36555a738239 · 2024-08-30T05:16:13.000Z
diff --git a/coderd/database/dbmem/dbmem.go b/coderd/database/dbmem/dbmem.go
@@ -1749,10 +1749,10 @@ func (q *FakeQuerier) DeleteOldWorkspaceAgentStats(_ context.Context) error {
 						-- use between 15 mins and 1 hour of data. We keep a
 						-- little bit more (1 day) just in case.
 						MAX(start_time) - '1 days'::interval,
-						-- Fall back to 6 months ago if there are no template
+						-- Fall back to ~6 months ago if there are no template
 						-- usage stats so that we don't delete the data before
 						-- it's rolled up.
-						NOW() - '6 months'::interval
+						NOW() - '180 days'::interval
 					)
 				FROM
 					template_usage_stats
@@ -1778,7 +1778,7 @@ func (q *FakeQuerier) DeleteOldWorkspaceAgentStats(_ context.Context) error {
 	}
 	// COALESCE
 	if limit.IsZero() {
-		limit = now.AddDate(0, -6, 0)
+		limit = now.AddDate(0, 0, -180)
 	}
 
 	var validStats []database.WorkspaceAgentStat
diff --git a/coderd/database/dbpurge/dbpurge_test.go b/coderd/database/dbpurge/dbpurge_test.go
@@ -86,27 +86,27 @@ func TestDeleteOldWorkspaceAgentStats(t *testing.T) {
 	// conflicts, verifying DST behavior is beyond the scope of this
 	// test.
 	// Let's use RxBytes to identify stat entries.
-	// Stat inserted 6 months + 2 hour ago, should be deleted.
+	// Stat inserted 180 days + 2 hour ago, should be deleted.
 	first := dbgen.WorkspaceAgentStat(t, db, database.WorkspaceAgentStat{
-		CreatedAt:                 now.AddDate(0, -6, 0).Add(-2 * time.Hour),
+		CreatedAt:                 now.AddDate(0, 0, -180).Add(-2 * time.Hour),
 		ConnectionCount:           1,
 		ConnectionMedianLatencyMS: 1,
 		RxBytes:                   1111,
 		SessionCountSSH:           1,
 	})
 
-	// Stat inserted 6 months - 2 hour ago, should not be deleted before rollup.
+	// Stat inserted 180 days - 2 hour ago, should not be deleted before rollup.
 	second := dbgen.WorkspaceAgentStat(t, db, database.WorkspaceAgentStat{
-		CreatedAt:                 now.AddDate(0, -6, 0).Add(2 * time.Hour),
+		CreatedAt:                 now.AddDate(0, 0, -180).Add(2 * time.Hour),
 		ConnectionCount:           1,
 		ConnectionMedianLatencyMS: 1,
 		RxBytes:                   2222,
 		SessionCountSSH:           1,
 	})
 
-	// Stat inserted 6 months - 1 day - 4 hour ago, should not be deleted at all.
+	// Stat inserted 179 days - 4 hour ago, should not be deleted at all.
 	third := dbgen.WorkspaceAgentStat(t, db, database.WorkspaceAgentStat{
-		CreatedAt:                 now.AddDate(0, -6, 0).AddDate(0, 0, 1).Add(4 * time.Hour),
+		CreatedAt:                 now.AddDate(0, 0, -179).Add(4 * time.Hour),
 		ConnectionCount:           1,
 		ConnectionMedianLatencyMS: 1,
 		RxBytes:                   3333,
@@ -121,8 +121,8 @@ func TestDeleteOldWorkspaceAgentStats(t *testing.T) {
 	var stats []database.GetWorkspaceAgentStatsRow
 	var err error
 	require.Eventuallyf(t, func() bool {
-		// Query all stats created not earlier than 7 months ago
-		stats, err = db.GetWorkspaceAgentStats(ctx, now.AddDate(0, -7, 0))
+		// Query all stats created not earlier than ~7 months ago
+		stats, err = db.GetWorkspaceAgentStats(ctx, now.AddDate(0, 0, -210))
 		if err != nil {
 			return false
 		}
@@ -144,8 +144,8 @@ func TestDeleteOldWorkspaceAgentStats(t *testing.T) {
 
 	// then
 	require.Eventuallyf(t, func() bool {
-		// Query all stats created not earlier than 7 months ago
-		stats, err = db.GetWorkspaceAgentStats(ctx, now.AddDate(0, -7, 0))
+		// Query all stats created not earlier than ~7 months ago
+		stats, err = db.GetWorkspaceAgentStats(ctx, now.AddDate(0, 0, -210))
 		if err != nil {
 			return false
 		}
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/workspaceagentstats.sql b/coderd/database/queries/workspaceagentstats.sql
@@ -78,10 +78,10 @@ WHERE
 				-- use between 15 mins and 1 hour of data. We keep a
 				-- little bit more (1 day) just in case.
 				MAX(start_time) - '1 days'::interval,
-				-- Fall back to 6 months ago if there are no template
+				-- Fall back to ~6 months ago if there are no template
 				-- usage stats so that we don't delete the data before
 				-- it's rolled up.
-				NOW() - '6 months'::interval
+				NOW() - '180 days'::interval
 			)
 		FROM
 			template_usage_stats
