chore: Add timezone param to DAU SQL query

Previously only worked for UTC timezone
coder · Emyrk · May 30, 2023 · May 23, 2023 · May 23, 2023 · May 23, 2023
commit f7f5d6652eb6bb70466e08a19bc705819794c310
diff --git a/coderd/database/dbauthz/system.go b/coderd/database/dbauthz/system.go
@@ -205,19 +205,19 @@ func (q *querier) GetTemplateAverageBuildTime(ctx context.Context, arg database.
 }
 
 // Only used by metrics cache.
-func (q *querier) GetTemplateDAUs(ctx context.Context, templateID uuid.UUID) ([]database.GetTemplateDAUsRow, error) {
+func (q *querier) GetTemplateDAUs(ctx context.Context, arg database.GetTemplateDAUsParams) ([]database.GetTemplateDAUsRow, error) {
 	if err := q.authorizeContext(ctx, rbac.ActionRead, rbac.ResourceSystem); err != nil {
 		return nil, err
 	}
-	return q.db.GetTemplateDAUs(ctx, templateID)
+	return q.db.GetTemplateDAUs(ctx, arg)
 }
 
 // Only used by metrics cache.
-func (q *querier) GetDeploymentDAUs(ctx context.Context) ([]database.GetDeploymentDAUsRow, error) {
+func (q *querier) GetDeploymentDAUs(ctx context.Context, tzOffset int32) ([]database.GetDeploymentDAUsRow, error) {
 	if err := q.authorizeContext(ctx, rbac.ActionRead, rbac.ResourceSystem); err != nil {
 		return nil, err
 	}
-	return q.db.GetDeploymentDAUs(ctx)
+	return q.db.GetDeploymentDAUs(ctx, tzOffset)
 }
 
 // UpdateWorkspaceBuildCostByID is used by the provisioning system to update the cost of a workspace build.

diff --git a/coderd/database/dbfake/databasefake.go b/coderd/database/dbfake/databasefake.go
@@ -434,21 +434,21 @@ func (q *fakeQuerier) InsertWorkspaceAgentStat(_ context.Context, p database.Ins
 	return stat, nil
 }
 
-func (q *fakeQuerier) GetTemplateDAUs(_ context.Context, templateID uuid.UUID) ([]database.GetTemplateDAUsRow, error) {
+func (q *fakeQuerier) GetTemplateDAUs(_ context.Context, arg database.GetTemplateDAUsParams) ([]database.GetTemplateDAUsRow, error) {
 	q.mutex.RLock()
 	defer q.mutex.RUnlock()
 
 	seens := make(map[time.Time]map[uuid.UUID]struct{})
 
 	for _, as := range q.workspaceAgentStats {
-		if as.TemplateID != templateID {
+		if as.TemplateID != arg.TemplateID {
 			continue
 		}
 		if as.ConnectionCount == 0 {
 			continue
 		}
 
-		date := as.CreatedAt.Truncate(time.Hour * 24)
+		date := as.CreatedAt.UTC().Add(time.Duration(arg.TzOffset) * time.Hour).Truncate(time.Hour * 24)
 
 		dateEntry := seens[date]
 		if dateEntry == nil {
@@ -477,7 +477,7 @@ func (q *fakeQuerier) GetTemplateDAUs(_ context.Context, templateID uuid.UUID) (
 	return rs, nil
 }
 
-func (q *fakeQuerier) GetDeploymentDAUs(_ context.Context) ([]database.GetDeploymentDAUsRow, error) {
+func (q *fakeQuerier) GetDeploymentDAUs(_ context.Context, tzOffset int32) ([]database.GetDeploymentDAUsRow, error) {
 	q.mutex.RLock()
 	defer q.mutex.RUnlock()
 
@@ -487,7 +487,7 @@ func (q *fakeQuerier) GetDeploymentDAUs(_ context.Context) ([]database.GetDeploy
 		if as.ConnectionCount == 0 {
 			continue
 		}
-		date := as.CreatedAt.Truncate(time.Hour * 24)
+		date := as.CreatedAt.UTC().Add(time.Duration(tzOffset) * time.Hour).Truncate(time.Hour * 24)
 
 		dateEntry := seens[date]
 		if dateEntry == nil {

diff --git a/coderd/database/dbmock/store.go b/coderd/database/dbmock/store.go
diff --git a/coderd/database/querier.go b/coderd/database/querier.go
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
@@ -24,7 +24,7 @@ VALUES
 
 -- name: GetTemplateDAUs :many
 SELECT
-	(created_at at TIME ZONE 'UTC')::date as date,
+	(created_at at TIME ZONE cast(@tz_offset::integer as text))::date as date,
 	user_id
 FROM
 	workspace_agent_stats
@@ -38,7 +38,7 @@ ORDER BY
 
 -- name: GetDeploymentDAUs :many
 SELECT
-	(created_at at TIME ZONE 'UTC')::date as date,
+	(created_at at TIME ZONE cast(@tz_offset::integer as text))::date as date,
 	user_id
 FROM
 	workspace_agent_stats

diff --git a/coderd/metricscache/metricscache.go b/coderd/metricscache/metricscache.go
@@ -180,15 +180,18 @@ func (c *Cache) refreshTemplateDAUs(ctx context.Context) error {
 		templateAverageBuildTimes = make(map[uuid.UUID]database.GetTemplateAverageBuildTimeRow)
 	)
 
-	rows, err := c.database.GetDeploymentDAUs(ctx)
+	rows, err := c.database.GetDeploymentDAUs(ctx, 0)
 	if err != nil {
 		return err
 	}
 	deploymentDAUs = convertDeploymentDAUResponse(rows)
 	c.deploymentDAUResponses.Store(&deploymentDAUs)
 
 	for _, template := range templates {
-		rows, err := c.database.GetTemplateDAUs(ctx, template.ID)
+		rows, err := c.database.GetTemplateDAUs(ctx, database.GetTemplateDAUsParams{
+			TemplateID: template.ID,
+			TzOffset:   0,
+		})
 		if err != nil {
 			return err
 		}