feat: Support different formats for time variables (#20626)

Przemysław Stępień · web-flow · commit 4242ce53144d · 2025-04-28T10:10:59.000Z
#### Summary ⚠️ **If you're contributing to a plugin please read this section of the [contribution guidelines](https://github.com/cloudquery/cloudquery/blob/main/CONTRIBUTING.md#open-core-vs-open-source) 🧑‍🎓 before submitting this PR** ⚠️ This PR follows changes introduced in #20399. In enables formatting substituted times using '|' operator according to golang time layout spec https://go.dev/src/time/format.go.
diff --git a/cli/internal/specs/v0/spec_reader.go b/cli/internal/specs/v0/spec_reader.go
@@ -77,12 +77,18 @@ func expandTime(cfg []byte) ([]byte, error) {
 	var expandErr error
 	cfg = timeRegex.ReplaceAllFunc(cfg, func(match []byte) []byte {
 		relativeTime := timeRegex.FindSubmatch(match)[1]
-		parsedTime, err := configtype.ParseTime(string(relativeTime))
+		splitString := strings.Split(string(relativeTime), "|")
+		relativeTimeString := splitString[0]
+		timeFormat := time.RFC3339
+		if len(splitString) > 1 {
+			timeFormat = splitString[1]
+		}
+		parsedTime, err := configtype.ParseTime(relativeTimeString)
 		if err != nil {
 			expandErr = errors.Join(errors.New("failed to substitute time"), err)
 			return nil
 		}
-		return []byte(parsedTime.AsTime(time.Now()).Format(time.RFC3339))
+		return []byte(parsedTime.AsTime(time.Now()).Format(timeFormat))
 	})
 	return cfg, expandErr
 }
diff --git a/cli/internal/specs/v0/spec_reader_test.go b/cli/internal/specs/v0/spec_reader_test.go
@@ -687,6 +687,39 @@ var specLoaderTestCases = []specLoaderTestCase{
 			},
 		},
 	},
+	{
+		name: "Time substitution with formatting",
+		path: []string{getPath("time_substitution_with_format.yml")},
+		err: func() string {
+			return ""
+		},
+		sources: []*Source{
+			{
+				Metadata: Metadata{
+					Name:             "aws",
+					Path:             "cloudquery/aws",
+					Version:          "v1.3.3",
+					Registry:         RegistryCloudQuery,
+					registryInferred: true,
+				},
+				Destinations: []string{"postgresql"},
+				Tables:       []string{"test"},
+				Spec:         map[string]any{"table_options": map[string]any{"aws_cloudtrail_events": map[string]any{"lookup_events": []any{map[string]any{"start_time": time.Date(2025, 1, 1, 0, 0, 0, 0, time.UTC).Format(time.DateOnly)}}}}},
+			},
+		},
+		destinations: []*Destination{
+			{
+				Metadata: Metadata{
+					Name:             "postgresql",
+					Path:             "cloudquery/postgresql",
+					Version:          "v1.6.3",
+					Registry:         RegistryCloudQuery,
+					registryInferred: true,
+				},
+				Spec: map[string]any{},
+			},
+		},
+	},
 	{
 		name: "Time substitution with error",
 		path: []string{getPath("time_substitution_with_error.yml")},
diff --git a/cli/internal/specs/v0/testdata/time_substitution_with_format.yml b/cli/internal/specs/v0/testdata/time_substitution_with_format.yml
@@ -0,0 +1,18 @@
+kind: source
+spec:
+  name: aws
+  version: v1.3.3
+  destinations: [postgresql]
+  path: cloudquery/aws
+  tables: [test]
+  spec:
+    table_options:
+      aws_cloudtrail_events:
+        lookup_events:
+          - start_time: ${time:2025-01-01|2006-01-02}  # Specifying non-relative time for easier testing
+---
+kind: destination
+spec:
+  name: postgresql
+  path: cloudquery/postgresql
+  version: v1.6.3
