Group user status counts by interval with support for a timezone offset

SasSwart · SasSwart · commit 6e67f5b70ded · 2025-01-15T14:12:09.000Z
diff --git a/coderd/database/dbauthz/dbauthz_test.go b/coderd/database/dbauthz/dbauthz_test.go
@@ -1712,6 +1712,8 @@ func (s *MethodTestSuite) TestUser() {
 		check.Args(database.GetUserStatusCountsParams{
 			StartTime: time.Now().Add(-time.Hour * 24 * 30),
 			EndTime:   time.Now(),
+			Interval:  int32((time.Hour * 24).Seconds()),
+			TzOffset:  0,
 		}).Asserts(rbac.ResourceUser, policy.ActionRead)
 	}))
 }
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
@@ -789,25 +789,12 @@ WITH
 	-- dates_of_interest defines all points in time that are relevant to the query.
 	-- It includes the start_time, all status changes, all deletions, and the end_time.
 dates_of_interest AS (
-	SELECT @start_time::timestamptz AS date
-
-	UNION
-
-    SELECT DISTINCT changed_at AS date
-    FROM user_status_changes
-    WHERE changed_at > @start_time::timestamptz
-        AND changed_at < @end_time::timestamptz
-
-	UNION
-
-	SELECT DISTINCT deleted_at AS date
-	FROM user_deleted
-	WHERE deleted_at > @start_time::timestamptz
-		AND deleted_at < @end_time::timestamptz
-
-	UNION
-
-	SELECT @end_time::timestamptz AS date
+	SELECT
+		(generate_series(
+			@start_time::timestamptz,
+			@end_time::timestamptz,
+			(@interval::int || ' seconds')::interval
+		) + (@tz_offset::int || ' seconds')::interval)::timestamptz AS date
 ),
 	-- latest_status_before_range defines the status of each user before the start_time.
 	-- We do not include users who were deleted before the start_time. We use this to ensure that
diff --git a/coderd/insights.go b/coderd/insights.go
@@ -306,6 +306,7 @@ func (api *API) insightsUserStatusCounts(rw http.ResponseWriter, r *http.Request
 	p := httpapi.NewQueryParamParser()
 	vals := r.URL.Query()
 	tzOffset := p.Int(vals, 0, "tz_offset")
+	interval := p.Int(vals, int((24 * time.Hour).Seconds()), "interval")
 	p.ErrorExcessParams(vals)
 
 	if len(p.Errors) > 0 {
@@ -327,6 +328,8 @@ func (api *API) insightsUserStatusCounts(rw http.ResponseWriter, r *http.Request
 	rows, err := api.Database.GetUserStatusCounts(ctx, database.GetUserStatusCountsParams{
 		StartTime: sixtyDaysAgo,
 		EndTime:   nextHourInLoc,
+		Interval:  int32(interval),
+		TzOffset:  int32(tzOffset),
 	})
 	if err != nil {
 		if httpapi.IsUnauthorizedError(err) {

Original file line number	Diff line number	Diff line change
`@@ -1712,6 +1712,8 @@ func (s *MethodTestSuite) TestUser() {`
`1712`	`1712`	`check.Args(database.GetUserStatusCountsParams{`
`1713`	`1713`	`StartTime: time.Now().Add(-time.Hour * 24 * 30),`
`1714`	`1714`	`EndTime: time.Now(),`
	`1715`	`+ Interval: int32((time.Hour * 24).Seconds()),`
	`1716`	`+ TzOffset: 0,`
`1715`	`1717`	`}).Asserts(rbac.ResourceUser, policy.ActionRead)`
`1716`	`1718`	`}))`
`1717`	`1719`	`}`