Skip to content

Commit f689b0d

Browse files
SasSwartSasSwart
committed
aggregate user status changes by predictable buckets instead of at the moment of each user status change
1 parent 3fa8ca7 commit f689b0d

File tree

6 files changed

+169
-150
lines changed

6 files changed

+169
-150
lines changed

‎coderd/database/dbauthz/dbauthz_test.go

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1713,7 +1713,6 @@ func (s *MethodTestSuite) TestUser() {
17131713
StartTime: time.Now().Add(-time.Hour * 24 * 30),
17141714
EndTime: time.Now(),
17151715
Interval: int32((time.Hour * 24).Seconds()),
1716-
TzOffset: 0,
17171716
}).Asserts(rbac.ResourceUser, policy.ActionRead)
17181717
}))
17191718
}

‎coderd/database/dbtime/dbtime.go

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -16,3 +16,9 @@ func Now() time.Time {
1616
func Time(t time.Time) time.Time {
1717
return t.Round(time.Microsecond)
1818
}
19+
20+
// StartOfDay returns the first timestamp of the day of the input timestamp in its location.
21+
func StartOfDay(t time.Time) time.Time {
22+
year, month, day := t.Date()
23+
return time.Date(year, month, day, 0, 0, 0, 0, t.Location())
24+
}

‎coderd/database/querier_test.go

Lines changed: 67 additions & 41 deletions
Original file line numberDiff line numberDiff line change
@@ -2454,28 +2454,26 @@ func TestGetUserStatusCounts(t *testing.T) {
24542454
UpdatedAt: createdAt,
24552455
})
24562456

2457-
_, offset := today.Zone()
24582457
userStatusChanges, err := db.GetUserStatusCounts(ctx, database.GetUserStatusCountsParams{
2459-
StartTime: startOfDay(createdAt),
2460-
EndTime: startOfDay(today),
2461-
TzOffset: int32(offset),
2458+
StartTime: dbtime.StartOfDay(createdAt),
2459+
EndTime: dbtime.StartOfDay(today),
24622460
})
24632461
require.NoError(t, err)
24642462

2465-
numDays := int(startOfDay(today).Sub(startOfDay(createdAt)).Hours() / 24)
2463+
numDays := int(dbtime.StartOfDay(today).Sub(dbtime.StartOfDay(createdAt)).Hours() / 24)
24662464
require.Len(t, userStatusChanges, numDays+1, "should have 1 entry per day between the start and end time, including the end time")
24672465

24682466
for i, row := range userStatusChanges {
24692467
require.Equal(t, tc.status, row.Status, "should have the correct status")
24702468
require.True(
24712469
t,
2472-
row.Date.In(location).Equal(startOfDay(createdAt).AddDate(0, 0, i)),
2470+
row.Date.In(location).Equal(dbtime.StartOfDay(createdAt).AddDate(0, 0, i)),
24732471
"expected date %s, but got %s for row %n",
2474-
startOfDay(createdAt).AddDate(0, 0, i),
2472+
dbtime.StartOfDay(createdAt).AddDate(0, 0, i),
24752473
row.Date.In(location).String(),
24762474
i,
24772475
)
2478-
if row.Date.Before(startOfDay(createdAt)) {
2476+
if row.Date.Before(createdAt) {
24792477
require.Equal(t, int64(0), row.Count, "should have 0 users before creation")
24802478
} else {
24812479
require.Equal(t, int64(1), row.Count, "should have 1 user after creation")
@@ -2634,24 +2632,38 @@ func TestGetUserStatusCounts(t *testing.T) {
26342632

26352633
// Query for the last 5 days
26362634
userStatusChanges, err := db.GetUserStatusCounts(ctx, database.GetUserStatusCountsParams{
2637-
StartTime: createdAt,
2638-
EndTime: today,
2635+
StartTime: dbtime.StartOfDay(createdAt),
2636+
EndTime: dbtime.StartOfDay(today),
26392637
})
26402638
require.NoError(t, err)
2641-
require.NotEmpty(t, userStatusChanges, "should return results")
26422639

2643-
gotCounts := map[time.Time]map[database.UserStatus]int64{}
2644-
for _, row := range userStatusChanges {
2645-
gotDateInLocation := row.Date.In(location)
2646-
if _, ok := gotCounts[gotDateInLocation]; !ok {
2647-
gotCounts[gotDateInLocation] = map[database.UserStatus]int64{}
2648-
}
2649-
if _, ok := gotCounts[gotDateInLocation][row.Status]; !ok {
2650-
gotCounts[gotDateInLocation][row.Status] = 0
2640+
for i, row := range userStatusChanges {
2641+
require.True(
2642+
t,
2643+
row.Date.In(location).Equal(dbtime.StartOfDay(createdAt).AddDate(0, 0, i/2)),
2644+
"expected date %s, but got %s for row %n",
2645+
dbtime.StartOfDay(createdAt).AddDate(0, 0, i/2),
2646+
row.Date.In(location).String(),
2647+
i,
2648+
)
2649+
if row.Date.Before(createdAt) {
2650+
require.Equal(t, int64(0), row.Count)
2651+
} else if row.Date.Before(firstTransitionTime) {
2652+
if row.Status == tc.initialStatus {
2653+
require.Equal(t, int64(1), row.Count)
2654+
} else if row.Status == tc.targetStatus {
2655+
require.Equal(t, int64(0), row.Count)
2656+
}
2657+
} else if !row.Date.After(today) {
2658+
if row.Status == tc.initialStatus {
2659+
require.Equal(t, int64(0), row.Count)
2660+
} else if row.Status == tc.targetStatus {
2661+
require.Equal(t, int64(1), row.Count)
2662+
}
2663+
} else {
2664+
t.Errorf("date %q beyond expected range end %q", row.Date, today)
26512665
}
2652-
gotCounts[gotDateInLocation][row.Status] += row.Count
26532666
}
2654-
require.Equal(t, tc.expectedCounts, gotCounts)
26552667
})
26562668
}
26572669
})
@@ -2840,16 +2852,23 @@ func TestGetUserStatusCounts(t *testing.T) {
28402852
})
28412853

28422854
userStatusChanges, err := db.GetUserStatusCounts(ctx, database.GetUserStatusCountsParams{
2843-
StartTime: createdAt.Add(time.Hour * 24),
2844-
EndTime: today,
2855+
StartTime: dbtime.StartOfDay(createdAt.Add(time.Hour * 24)),
2856+
EndTime: dbtime.StartOfDay(today),
28452857
})
28462858
require.NoError(t, err)
28472859

2848-
require.Len(t, userStatusChanges, 2)
2849-
require.Equal(t, userStatusChanges[0].Count, int64(1))
2850-
require.Equal(t, userStatusChanges[0].Status, database.UserStatusActive)
2851-
require.Equal(t, userStatusChanges[1].Count, int64(1))
2852-
require.Equal(t, userStatusChanges[1].Status, database.UserStatusActive)
2860+
for i, row := range userStatusChanges {
2861+
require.True(
2862+
t,
2863+
row.Date.In(location).Equal(dbtime.StartOfDay(createdAt).AddDate(0, 0, 1+i)),
2864+
"expected date %s, but got %s for row %n",
2865+
dbtime.StartOfDay(createdAt).AddDate(0, 0, 1+i),
2866+
row.Date.In(location).String(),
2867+
i,
2868+
)
2869+
require.Equal(t, database.UserStatusActive, row.Status)
2870+
require.Equal(t, int64(1), row.Count)
2871+
}
28532872
})
28542873

28552874
t.Run("User deleted before query range", func(t *testing.T) {
@@ -2889,16 +2908,28 @@ func TestGetUserStatusCounts(t *testing.T) {
28892908
require.NoError(t, err)
28902909

28912910
userStatusChanges, err := db.GetUserStatusCounts(ctx, database.GetUserStatusCountsParams{
2892-
StartTime: createdAt,
2893-
EndTime: today.Add(time.Hour * 24),
2911+
StartTime: dbtime.StartOfDay(createdAt),
2912+
EndTime: dbtime.StartOfDay(today.Add(time.Hour * 24)),
28942913
})
28952914
require.NoError(t, err)
2896-
require.Equal(t, userStatusChanges[0].Count, int64(1))
2897-
require.Equal(t, userStatusChanges[0].Status, database.UserStatusActive)
2898-
require.Equal(t, userStatusChanges[1].Count, int64(0))
2899-
require.Equal(t, userStatusChanges[1].Status, database.UserStatusActive)
2900-
require.Equal(t, userStatusChanges[2].Count, int64(0))
2901-
require.Equal(t, userStatusChanges[2].Status, database.UserStatusActive)
2915+
for i, row := range userStatusChanges {
2916+
require.True(
2917+
t,
2918+
row.Date.In(location).Equal(dbtime.StartOfDay(createdAt).AddDate(0, 0, i)),
2919+
"expected date %s, but got %s for row %n",
2920+
dbtime.StartOfDay(createdAt).AddDate(0, 0, i),
2921+
row.Date.In(location).String(),
2922+
i,
2923+
)
2924+
require.Equal(t, database.UserStatusActive, row.Status)
2925+
if row.Date.Before(createdAt) {
2926+
require.Equal(t, int64(0), row.Count)
2927+
} else if i == len(userStatusChanges)-1 {
2928+
require.Equal(t, int64(0), row.Count)
2929+
} else {
2930+
require.Equal(t, int64(1), row.Count)
2931+
}
2932+
}
29022933
})
29032934
})
29042935
}
@@ -2908,8 +2939,3 @@ func requireUsersMatch(t testing.TB, expected []database.User, found []database.
29082939
t.Helper()
29092940
require.ElementsMatch(t, expected, database.ConvertUserRows(found), msg)
29102941
}
2911-
2912-
func startOfDay(t time.Time) time.Time {
2913-
year, month, day := t.Date()
2914-
return time.Date(year, month, day, 0, 0, 0, 0, t.Location())
2915-
}

‎coderd/database/queries.sql.go

Lines changed: 7 additions & 14 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

0 commit comments

Comments
 (0)