update the query to created_at_before and created_at_after

coder · ethanndickson · Dec 17, 2024 · Nov 22, 2024 · Dec 4, 2024 · Dec 5, 2024
commit e29a208749c1d72bd7f4abb3a27bb4a57201d43c
diff --git a/coderd/database/dbmem/dbmem.go b/coderd/database/dbmem/dbmem.go
@@ -5648,10 +5648,20 @@ func (q *FakeQuerier) GetUsers(_ context.Context, params database.GetUsersParams
 		users = usersFilteredByRole
 	}
 
-	if !params.CreatedAt.IsZero() {
+	if !params.CreatedAtBefore.IsZero() {
 		usersFilteredByCreatedAt := make([]database.User, 0, len(users))
 		for i, user := range users {
-			if user.CreatedAt.Equal(params.CreatedAt) {
+			if user.CreatedAt.Before(params.CreatedAtBefore) {
+				usersFilteredByCreatedAt = append(usersFilteredByCreatedAt, users[i])
+			}
+		}
+		users = usersFilteredByCreatedAt
+	}
+
+	if !params.CreatedAtAfter.IsZero() {
+		usersFilteredByCreatedAt := make([]database.User, 0, len(users))
+		for i, user := range users {
+			if user.CreatedAt.After(params.CreatedAtAfter) {
 				usersFilteredByCreatedAt = append(usersFilteredByCreatedAt, users[i])
 			}
 		}

diff --git a/coderd/database/modelqueries.go b/coderd/database/modelqueries.go
@@ -390,7 +390,8 @@ func (q *sqlQuerier) GetAuthorizedUsers(ctx context.Context, arg GetUsersParams,
 		pq.Array(arg.RbacRole),
 		arg.LastSeenBefore,
 		arg.LastSeenAfter,
-		arg.CreatedAt,
+		arg.CreatedAtBefore,
+		arg.CreatedAtAfter,
 		arg.OffsetOpt,
 		arg.LimitOpt,
 	)

diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/users.sql b/coderd/database/queries/users.sql
@@ -201,8 +201,13 @@ WHERE
 	END
 	-- Filter by created_at
 	AND CASE
-		WHEN @created_at :: timestamp with time zone != '0001-01-01 00:00:00Z' THEN
-			DATE(created_at) = DATE(@created_at)
+		WHEN @created_at_before :: timestamp with time zone != '0001-01-01 00:00:00Z' THEN
+			created_at <= @created_at_before
+		ELSE true
+	END
+	AND CASE
+		WHEN @created_at_after :: timestamp with time zone != '0001-01-01 00:00:00Z' THEN
+			created_at >= @created_at_after
 		ELSE true
 	END
 	-- End of filters

diff --git a/coderd/searchquery/search.go b/coderd/searchquery/search.go
@@ -65,12 +65,13 @@ func Users(query string) (database.GetUsersParams, []codersdk.ValidationError) {
 
 	parser := httpapi.NewQueryParamParser()
 	filter := database.GetUsersParams{
-		Search:         parser.String(values, "", "search"),
-		Status:         httpapi.ParseCustomList(parser, values, []database.UserStatus{}, "status", httpapi.ParseEnum[database.UserStatus]),
-		RbacRole:       parser.Strings(values, []string{}, "role"),
-		LastSeenAfter:  parser.Time3339Nano(values, time.Time{}, "last_seen_after"),
-		LastSeenBefore: parser.Time3339Nano(values, time.Time{}, "last_seen_before"),
-		CreatedAt:      parser.Time3339Nano(values, time.Time{}, "created_at"),
+		Search:          parser.String(values, "", "search"),
+		Status:          httpapi.ParseCustomList(parser, values, []database.UserStatus{}, "status", httpapi.ParseEnum[database.UserStatus]),
+		RbacRole:        parser.Strings(values, []string{}, "role"),
+		LastSeenAfter:   parser.Time3339Nano(values, time.Time{}, "last_seen_after"),
+		LastSeenBefore:  parser.Time3339Nano(values, time.Time{}, "last_seen_before"),
+		CreatedAtAfter:  parser.Time3339Nano(values, time.Time{}, "created_at_after"),
+		CreatedAtBefore: parser.Time3339Nano(values, time.Time{}, "created_at_before"),
 	}
 	parser.ErrorExcessParams(values)
 	return filter, parser.Errors

diff --git a/coderd/users.go b/coderd/users.go
@@ -311,15 +311,16 @@ func (api *API) GetUsers(rw http.ResponseWriter, r *http.Request) ([]database.Us
 	}
 
 	userRows, err := api.Database.GetUsers(ctx, database.GetUsersParams{
-		AfterID:        paginationParams.AfterID,
-		Search:         params.Search,
-		Status:         params.Status,
-		RbacRole:       params.RbacRole,
-		LastSeenBefore: params.LastSeenBefore,
-		LastSeenAfter:  params.LastSeenAfter,
-		CreatedAt:      params.CreatedAt,
-		OffsetOpt:      int32(paginationParams.Offset),
-		LimitOpt:       int32(paginationParams.Limit),
+		AfterID:         paginationParams.AfterID,
+		Search:          params.Search,
+		Status:          params.Status,
+		RbacRole:        params.RbacRole,
+		LastSeenBefore:  params.LastSeenBefore,
+		LastSeenAfter:   params.LastSeenAfter,
+		CreatedAtAfter:  params.CreatedAtAfter,
+		CreatedAtBefore: params.CreatedAtBefore,
+		OffsetOpt:       int32(paginationParams.Offset),
+		LimitOpt:        int32(paginationParams.Limit),
 	})
 	if err != nil {
 		httpapi.Write(ctx, rw, http.StatusInternalServerError, codersdk.Response{

diff --git a/coderd/users_test.go b/coderd/users_test.go
@@ -1467,7 +1467,6 @@ func TestUsersFilter(t *testing.T) {
 
 	firstUser, err := client.User(ctx, codersdk.Me)
 	require.NoError(t, err, "fetch me")
-	createdAt := firstUser.CreatedAt
 
 	// Noon on Jan 18 is the "now" for this test for last_seen timestamps.
 	// All these values are equal
@@ -1657,13 +1656,34 @@ func TestUsersFilter(t *testing.T) {
 			},
 		},
 		{
-			Name: "CreatedAt",
+			Name: "CreatedAtBefore",
 			Filter: codersdk.UsersRequest{
-				SearchQuery: fmt.Sprintf(`created_at:%q`, createdAt.Format(time.RFC3339)),
+				SearchQuery: `created_at_before:"2023-01-31T23:59:59Z"`,
 			},
 			FilterF: func(_ codersdk.UsersRequest, u codersdk.User) bool {
-				target := time.Date(2023, 1, 18, 12, 0, 0, 0, time.UTC)
-				return u.CreatedAt.Equal(target)
+				end := time.Date(2023, 1, 31, 23, 59, 59, 0, time.UTC)
+				return u.CreatedAt.Before(end)
+			},
+		},
+		{
+			Name: "CreatedAtAfter",
+			Filter: codersdk.UsersRequest{
+				SearchQuery: `created_at_after:"2023-01-01T00:00:00Z"`,
+			},
+			FilterF: func(_ codersdk.UsersRequest, u codersdk.User) bool {
+				start := time.Date(2023, 1, 1, 0, 0, 0, 0, time.UTC)
+				return u.CreatedAt.After(start)
+			},
+		},
+		{
+			Name: "CreatedAtRange",
+			Filter: codersdk.UsersRequest{
+				SearchQuery: `created_at_after:"2023-01-01T00:00:00Z" created_at_before:"2023-01-31T23:59:59Z"`,
+			},
+			FilterF: func(_ codersdk.UsersRequest, u codersdk.User) bool {
+				start := time.Date(2023, 1, 1, 0, 0, 0, 0, time.UTC)
+				end := time.Date(2023, 1, 31, 23, 59, 59, 0, time.UTC)
+				return u.CreatedAt.After(start) && u.CreatedAt.Before(end)
 			},
 		},
 	}

diff --git a/docs/admin/users/index.md b/docs/admin/users/index.md
@@ -187,7 +187,8 @@ to use the Coder's filter query:
 - To find admin users, use the filter `role:admin`.
 - To find users who have not been active since July 2023:
   `status:active last_seen_before:"2023-07-01T00:00:00Z"`
-- To find users created at January 18, 2023: `created_at:"2023-01-18T00:00:00Z"`
+- To find users who were created between January 1 and January 18, 2023:
+  `created_at_before:"2023-01-18T00:00:00Z" created_at_after:"2023-01-01T23:59:59Z"`
 
 The following filters are supported:
 
@@ -199,4 +200,5 @@ The following filters are supported:
 - `last_seen_before` and `last_seen_after` - The last time a user has used the
   platform (e.g. logging in, any API requests, connecting to workspaces). Uses
   the RFC3339Nano format.
-- `created_at` - The time a user was created. Uses the RFC3339Nano format.
+- `created_at_before` and `created_at_after` - The time a user was created. Uses
+  the RFC3339Nano format.