coder · johnstcn · Sep 15, 2023 · Sep 13, 2023 · Sep 14, 2023 · Sep 14, 2023
diff --git a/cli/clitest/golden.go b/cli/clitest/golden.go
@@ -28,7 +28,7 @@ import (
 // make update-golden-files
 var UpdateGoldenFiles = flag.Bool("update", false, "update .golden files")
 
-var timestampRegex = regexp.MustCompile(`(?i)\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}(.\d+)?Z`)
+var timestampRegex = regexp.MustCompile(`(?i)\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}(.\d+)?(Z|[+-]\d+:\d+)`)
 
 type CommandHelpCase struct {
 	Name string

diff --git a/coderd/activitybump_internal_test.go b/coderd/activitybump_internal_test.go
@@ -22,6 +22,15 @@ import (
 func Test_ActivityBumpWorkspace(t *testing.T) {
 	t.Parallel()
 
+	// We test the below in multiple timezones
+	timezones := []string{
+		"Asia/Kolkata",
+		"Canada/Newfoundland",
+		"Europe/Paris",
+		"US/Central",
+		"UTC",
+	}
+
 	for _, tt := range []struct {
 		name                string
 		transition          database.WorkspaceTransition
@@ -92,117 +101,120 @@ func Test_ActivityBumpWorkspace(t *testing.T) {
 		},
 	} {
 		tt := tt
-		t.Run(tt.name, func(t *testing.T) {
-			t.Parallel()
-
-			var (
-				now   = dbtime.Now()
-				ctx   = testutil.Context(t, testutil.WaitShort)
-				log   = slogtest.Make(t, nil)
-				db, _ = dbtestutil.NewDB(t)
-				org   = dbgen.Organization(t, db, database.Organization{})
-				user  = dbgen.User(t, db, database.User{
-					Status: database.UserStatusActive,
-				})
-				_ = dbgen.OrganizationMember(t, db, database.OrganizationMember{
-					UserID:         user.ID,
-					OrganizationID: org.ID,
-				})
-				templateVersion = dbgen.TemplateVersion(t, db, database.TemplateVersion{
-					OrganizationID: org.ID,
-					CreatedBy:      user.ID,
-				})
-				template = dbgen.Template(t, db, database.Template{
-					OrganizationID:  org.ID,
-					ActiveVersionID: templateVersion.ID,
-					CreatedBy:       user.ID,
-				})
-				ws = dbgen.Workspace(t, db, database.Workspace{
-					OwnerID:        user.ID,
-					OrganizationID: org.ID,
-					TemplateID:     template.ID,
-					Ttl:            sql.NullInt64{Valid: true, Int64: int64(tt.workspaceTTL)},
-				})
-				job = dbgen.ProvisionerJob(t, db, database.ProvisionerJob{
-					OrganizationID: org.ID,
-					CompletedAt:    tt.jobCompletedAt,
-				})
-				_ = dbgen.WorkspaceResource(t, db, database.WorkspaceResource{
-					JobID: job.ID,
+		for _, tz := range timezones {
+			tz := tz
+			t.Run(tt.name+"/"+tz, func(t *testing.T) {
+				t.Parallel()
+
+				var (
+					now   = dbtime.Now()
+					ctx   = testutil.Context(t, testutil.WaitShort)
+					log   = slogtest.Make(t, nil)
+					db, _ = dbtestutil.NewDB(t, dbtestutil.WithTimezone(tz))
+					org   = dbgen.Organization(t, db, database.Organization{})
+					user  = dbgen.User(t, db, database.User{
+						Status: database.UserStatusActive,
+					})
+					_ = dbgen.OrganizationMember(t, db, database.OrganizationMember{
+						UserID:         user.ID,
+						OrganizationID: org.ID,
+					})
+					templateVersion = dbgen.TemplateVersion(t, db, database.TemplateVersion{
+						OrganizationID: org.ID,
+						CreatedBy:      user.ID,
+					})
+					template = dbgen.Template(t, db, database.Template{
+						OrganizationID:  org.ID,
+						ActiveVersionID: templateVersion.ID,
+						CreatedBy:       user.ID,
+					})
+					ws = dbgen.Workspace(t, db, database.Workspace{
+						OwnerID:        user.ID,
+						OrganizationID: org.ID,
+						TemplateID:     template.ID,
+						Ttl:            sql.NullInt64{Valid: true, Int64: int64(tt.workspaceTTL)},
+					})
+					job = dbgen.ProvisionerJob(t, db, database.ProvisionerJob{
+						OrganizationID: org.ID,
+						CompletedAt:    tt.jobCompletedAt,
+					})
+					_ = dbgen.WorkspaceResource(t, db, database.WorkspaceResource{
+						JobID: job.ID,
+					})
+					buildID = uuid.New()
+				)
+
+				var buildNumber int32 = 1
+				// Insert a number of previous workspace builds.
+				for i := 0; i < 5; i++ {
+					insertPrevWorkspaceBuild(t, db, org.ID, templateVersion.ID, ws.ID, database.WorkspaceTransitionStart, buildNumber)
+					buildNumber++
+					insertPrevWorkspaceBuild(t, db, org.ID, templateVersion.ID, ws.ID, database.WorkspaceTransitionStop, buildNumber)
+					buildNumber++
+				}
+
+				// dbgen.WorkspaceBuild automatically sets deadline to now+1 hour if not set
+				var buildDeadline time.Time
+				if tt.buildDeadlineOffset != nil {
+					buildDeadline = now.Add(*tt.buildDeadlineOffset)
+				}
+				var maxDeadline time.Time
+				if tt.maxDeadlineOffset != nil {
+					maxDeadline = now.Add(*tt.maxDeadlineOffset)
+				}
+				err := db.InsertWorkspaceBuild(ctx, database.InsertWorkspaceBuildParams{
+					ID:                buildID,
+					CreatedAt:         dbtime.Now(),
+					UpdatedAt:         dbtime.Now(),
+					BuildNumber:       buildNumber,
+					InitiatorID:       user.ID,
+					Reason:            database.BuildReasonInitiator,
+					WorkspaceID:       ws.ID,
+					JobID:             job.ID,
+					TemplateVersionID: templateVersion.ID,
+					Transition:        tt.transition,
+					Deadline:          buildDeadline,
+					MaxDeadline:       maxDeadline,
 				})
-				buildID = uuid.New()
-			)
-
-			var buildNumber int32 = 1
-			// Insert a number of previous workspace builds.
-			for i := 0; i < 5; i++ {
-				insertPrevWorkspaceBuild(t, db, org.ID, templateVersion.ID, ws.ID, database.WorkspaceTransitionStart, buildNumber)
-				buildNumber++
-				insertPrevWorkspaceBuild(t, db, org.ID, templateVersion.ID, ws.ID, database.WorkspaceTransitionStop, buildNumber)
-				buildNumber++
-			}
-
-			// dbgen.WorkspaceBuild automatically sets deadline to now+1 hour if not set
-			var buildDeadline time.Time
-			if tt.buildDeadlineOffset != nil {
-				buildDeadline = now.Add(*tt.buildDeadlineOffset)
-			}
-			var maxDeadline time.Time
-			if tt.maxDeadlineOffset != nil {
-				maxDeadline = now.Add(*tt.maxDeadlineOffset)
-			}
-			err := db.InsertWorkspaceBuild(ctx, database.InsertWorkspaceBuildParams{
-				ID:                buildID,
-				CreatedAt:         dbtime.Now(),
-				UpdatedAt:         dbtime.Now(),
-				BuildNumber:       buildNumber,
-				InitiatorID:       user.ID,
-				Reason:            database.BuildReasonInitiator,
-				WorkspaceID:       ws.ID,
-				JobID:             job.ID,
-				TemplateVersionID: templateVersion.ID,
-				Transition:        tt.transition,
-				Deadline:          buildDeadline,
-				MaxDeadline:       maxDeadline,
+				require.NoError(t, err, "unexpected error inserting workspace build")
+				bld, err := db.GetWorkspaceBuildByID(ctx, buildID)
+				require.NoError(t, err, "unexpected error fetching inserted workspace build")
+
+				// Validate our initial state before bump
+				require.Equal(t, tt.transition, bld.Transition, "unexpected transition before bump")
+				require.Equal(t, tt.jobCompletedAt.Time.UTC(), job.CompletedAt.Time.UTC(), "unexpected job completed at before bump")
+				require.Equal(t, buildDeadline.UTC(), bld.Deadline.UTC(), "unexpected build deadline before bump")
+				require.Equal(t, maxDeadline.UTC(), bld.MaxDeadline.UTC(), "unexpected max deadline before bump")
+				require.Equal(t, tt.workspaceTTL, time.Duration(ws.Ttl.Int64), "unexpected workspace TTL before bump")
+
+				workaroundWindowsTimeResolution(t)
+
+				// Bump duration is measured from the time of the bump, so we measure from here.
+				start := dbtime.Now()
+				activityBumpWorkspace(ctx, log, db, bld.WorkspaceID)
+				elapsed := time.Since(start)
+				if elapsed > 15*time.Second {
+					t.Logf("warning: activityBumpWorkspace took longer than 15 seconds: %s", elapsed)
+				}
+				// The actual bump could have happened anywhere in the elapsed time, so we
+				// guess at the approximate time of the bump.
+				approxBumpTime := start.Add(elapsed / 2)
+
+				// Validate our state after bump
+				updatedBuild, err := db.GetLatestWorkspaceBuildByWorkspaceID(ctx, bld.WorkspaceID)
+				require.NoError(t, err, "unexpected error getting latest workspace build")
+				if tt.expectedBump == 0 {
+					require.Equal(t, bld.UpdatedAt.UTC(), updatedBuild.UpdatedAt.UTC(), "should not have bumped updated_at")
+					require.Equal(t, bld.Deadline.UTC(), updatedBuild.Deadline.UTC(), "should not have bumped deadline")
+				} else {
+					require.NotEqual(t, bld.UpdatedAt.UTC(), updatedBuild.UpdatedAt.UTC(), "should have bumped updated_at")
+					expectedDeadline := approxBumpTime.Add(tt.expectedBump).UTC()
+					// Note: if CI is especially slow, this test may fail. There is an internal 15-second
+					// deadline in activityBumpWorkspace, so we allow the same window here.
+					require.WithinDuration(t, expectedDeadline, updatedBuild.Deadline.UTC(), 15*time.Second, "unexpected deadline after bump")
+				}
 			})
-			require.NoError(t, err, "unexpected error inserting workspace build")
-			bld, err := db.GetWorkspaceBuildByID(ctx, buildID)
-			require.NoError(t, err, "unexpected error fetching inserted workspace build")
-
-			// Validate our initial state before bump
-			require.Equal(t, tt.transition, bld.Transition, "unexpected transition before bump")
-			require.Equal(t, tt.jobCompletedAt.Time.UTC(), job.CompletedAt.Time.UTC(), "unexpected job completed at before bump")
-			require.Equal(t, buildDeadline.UTC(), bld.Deadline.UTC(), "unexpected build deadline before bump")
-			require.Equal(t, maxDeadline.UTC(), bld.MaxDeadline.UTC(), "unexpected max deadline before bump")
-			require.Equal(t, tt.workspaceTTL, time.Duration(ws.Ttl.Int64), "unexpected workspace TTL before bump")
-
-			workaroundWindowsTimeResolution(t)
-
-			// Bump duration is measured from the time of the bump, so we measure from here.
-			start := dbtime.Now()
-			activityBumpWorkspace(ctx, log, db, bld.WorkspaceID)
-			elapsed := time.Since(start)
-			if elapsed > 15*time.Second {
-				t.Logf("warning: activityBumpWorkspace took longer than 15 seconds: %s", elapsed)
-			}
-			// The actual bump could have happened anywhere in the elapsed time, so we
-			// guess at the approximate time of the bump.
-			approxBumpTime := start.Add(elapsed / 2)
-
-			// Validate our state after bump
-			updatedBuild, err := db.GetLatestWorkspaceBuildByWorkspaceID(ctx, bld.WorkspaceID)
-			require.NoError(t, err, "unexpected error getting latest workspace build")
-			if tt.expectedBump == 0 {
-				require.Equal(t, bld.UpdatedAt.UTC(), updatedBuild.UpdatedAt.UTC(), "should not have bumped updated_at")
-				require.Equal(t, bld.Deadline.UTC(), updatedBuild.Deadline.UTC(), "should not have bumped deadline")
-			} else {
-				require.NotEqual(t, bld.UpdatedAt.UTC(), updatedBuild.UpdatedAt.UTC(), "should have bumped updated_at")
-				expectedDeadline := approxBumpTime.Add(tt.expectedBump).UTC()
-				// Note: if CI is especially slow, this test may fail. There is an internal 15-second
-				// deadline in activityBumpWorkspace, so we allow the same window here.
-				require.WithinDuration(t, expectedDeadline, updatedBuild.Deadline.UTC(), 15*time.Second, "unexpected deadline after bump")
-			}
-		})
+		}
 	}
 }
 

diff --git a/coderd/activitybump_test.go b/coderd/activitybump_test.go
@@ -30,6 +30,7 @@ func TestWorkspaceActivityBump(t *testing.T) {
 	// doesn't use template autostop requirements and instead edits the
 	// max_deadline on the build directly in the database.
 	setupActivityTest := func(t *testing.T, deadline ...time.Duration) (client *codersdk.Client, workspace codersdk.Workspace, assertBumped func(want bool)) {
+		t.Helper()
 		const ttl = time.Minute
 		maxTTL := time.Duration(0)
 		if len(deadline) > 0 {
@@ -120,6 +121,7 @@ func TestWorkspaceActivityBump(t *testing.T) {
 		_ = coderdtest.AwaitWorkspaceAgents(t, client, workspace.ID)
 
 		return client, workspace, func(want bool) {
+			t.Helper()
 			if !want {
 				// It is difficult to test the absence of a call in a non-racey
 				// way. In general, it is difficult for the API to generate

diff --git a/coderd/database/dbtestutil/db.go b/coderd/database/dbtestutil/db.go
@@ -3,7 +3,10 @@ package dbtestutil
 import (
 	"context"
 	"database/sql"
+	"fmt"
+	"net/url"
 	"os"
+	"strings"
 	"testing"
 
 	"github.com/stretchr/testify/require"
@@ -19,9 +22,30 @@ func WillUsePostgres() bool {
 	return os.Getenv("DB") != ""
 }
 
-func NewDB(t testing.TB) (database.Store, pubsub.Pubsub) {
+type options struct {
+	fixedTimezone string
+}
+
+type Option func(*options)
+
+// WithTimezone sets the database to the defined timezone instead of
+// to a random one.
+//
+// Deprecated: If you need to use this, you may have a timezone-related bug.
+func WithTimezone(tz string) Option {
+	return func(o *options) {
+		o.fixedTimezone = tz
+	}
+}
+
+func NewDB(t testing.TB, opts ...Option) (database.Store, pubsub.Pubsub) {
 	t.Helper()
 
+	var o options
+	for _, opt := range opts {
+		opt(&o)
+	}
+
 	db := dbfake.New()
 	ps := pubsub.NewInMemory()
 	if WillUsePostgres() {
@@ -35,6 +59,19 @@ func NewDB(t testing.TB) (database.Store, pubsub.Pubsub) {
 			require.NoError(t, err)
 			t.Cleanup(closePg)
 		}
+
+		if o.fixedTimezone == "" {
+			// To make sure we find timezone-related issues, we set the timezone
+			// of the database to a non-UTC one.
+			// The below was picked due to the following properties:
+			// - It has a non-UTC offset
+			// - It has a fractional hour UTC offset
+			// - It includes a daylight savings time component
+			o.fixedTimezone = "Canada/Newfoundland"
+		}
+		dbName := dbNameFromConnectionURL(t, connectionURL)
+		setDBTimezone(t, connectionURL, dbName, o.fixedTimezone)
+
 		sqlDB, err := sql.Open("postgres", connectionURL)
 		require.NoError(t, err)
 		t.Cleanup(func() {
@@ -51,3 +88,28 @@ func NewDB(t testing.TB) (database.Store, pubsub.Pubsub) {
 
 	return db, ps
 }
+
+// setRandDBTimezone sets the timezone of the database to the given timezone.
+func setDBTimezone(t testing.TB, dbURL, dbname, tz string) {
+	t.Helper()
+
+	sqlDB, err := sql.Open("postgres", dbURL)
+	require.NoError(t, err)
+	defer func() {
+		// The updated timezone only comes into effect on reconnect, so close
+		// the DB after we're done.
+		_ = sqlDB.Close()
+	}()
+
+	// nolint: gosec // This unfortunately does not work with placeholders.
+	_, err = sqlDB.Exec(fmt.Sprintf("ALTER DATABASE %s SET TIMEZONE TO %q", dbname, tz))
+	require.NoError(t, err, "failed to set timezone for database")
+}
+
+// dbNameFromConnectionURL returns the database name from the given connection URL,
+// where connectionURL is of the form postgres://user:pass@host:port/dbname
+func dbNameFromConnectionURL(t testing.TB, connectionURL string) string {
+	u, err := url.Parse(connectionURL)
+	require.NoError(t, err)
+	return strings.TrimPrefix(u.Path, "/")
+}