fix(bigquery): support more timestamp formats for query param (#9236)

BigQuery accepts timestamp parameters in many different formats, so when parsing the query parameters, we need to be able to support all of them. As reported on #9221, if a query is created using bq-cli (or any other system) with the `time.DateTime(2006-01-02 15:04:05)` format, our SDK was failing to parse the Job Config when retrieving it from the backend system. Resolves #9221
googleapis · Jan 24, 2024 · cc98509 · cc98509
1 parent 929d817
commit cc98509
Show file tree

Hide file tree

Showing 2 changed files with 28 additions and 6 deletions.
diff --git a/bigquery/integration_test.go b/bigquery/integration_test.go
@@ -2473,6 +2473,22 @@ func TestIntegration_TimestampFormat(t *testing.T) {
 			[]Value{ts},
 			ts,
 		},
+		{
+			"SELECT @val",
+			[]*bq.QueryParameter{
+				{
+					Name: "val",
+					ParameterType: &bq.QueryParameterType{
+						Type: "TIMESTAMP",
+					},
+					ParameterValue: &bq.QueryParameterValue{
+						Value: ts.Format(dateTimeFormat),
+					},
+				},
+			},
+			[]Value{ts},
+			ts,
+		},
 		{
 			"SELECT @val",
 			[]*bq.QueryParameter{

diff --git a/bigquery/params.go b/bigquery/params.go
@@ -29,10 +29,13 @@ import (
 	bq "google.golang.org/api/bigquery/v2"
 )
 
+// See https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#timestamp-type.
 var (
-	// See https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#timestamp-type.
 	timestampFormat = "2006-01-02 15:04:05.999999-07:00"
+	dateTimeFormat  = "2006-01-02 15:04:05"
+)
 
+var (
 	// See https://cloud.google.com/bigquery/docs/reference/rest/v2/tables#schema.fields.name
 	validFieldName = regexp.MustCompile("^[a-zA-Z_][a-zA-Z0-9_]{0,127}$")
 )
@@ -590,14 +593,17 @@ func convertParamValue(qval *bq.QueryParameterValue, qtype *bq.QueryParameterTyp
 		if isNullScalar(qval) {
 			return NullTimestamp{Valid: false}, nil
 		}
-		t, err := time.Parse(timestampFormat, qval.Value)
-		if err != nil {
-			t, err = time.Parse(time.RFC3339Nano, qval.Value)
+		formats := []string{timestampFormat, time.RFC3339Nano, dateTimeFormat}
+		var lastParseErr error
+		for _, format := range formats {
+			t, err := time.Parse(format, qval.Value)
 			if err != nil {
-				return nil, err
+				lastParseErr = err
+				continue
 			}
+			return t, nil
 		}
-		return t, nil
+		return nil, lastParseErr
 
 	case "DATETIME":
 		if isNullScalar(qval) {