fix(bigquery): parse timestamps with timezone info (#11950)

alvarowolfx · web-flow · commit 530d522a1f86 · 2025-04-08T13:21:23.000-04:00
diff --git a/bigquery/integration_test.go b/bigquery/integration_test.go
@@ -2603,7 +2603,8 @@ func TestIntegration_TimestampFormat(t *testing.T) {
 		t.Skip("Integration tests skipped")
 	}
 	ctx := context.Background()
-	ts := time.Date(2020, 10, 15, 15, 04, 05, 0, time.UTC)
+	ts := time.Date(2020, 1, 2, 15, 04, 05, 0, time.UTC)
+	cdt := civil.DateOf(ts)
 
 	testCases := []struct {
 		name       string
@@ -2680,6 +2681,40 @@ func TestIntegration_TimestampFormat(t *testing.T) {
 			[]Value{ts},
 			ts,
 		},
+		{
+			"Date with optional leading zero",
+			"SELECT @val",
+			[]*bq.QueryParameter{
+				{
+					Name: "val",
+					ParameterType: &bq.QueryParameterType{
+						Type: "DATE",
+					},
+					ParameterValue: &bq.QueryParameterValue{
+						Value: ts.Format("2006-1-2"),
+					},
+				},
+			},
+			[]Value{cdt},
+			cdt,
+		},
+		{
+			"Datetime with TZ",
+			"SELECT @val",
+			[]*bq.QueryParameter{
+				{
+					Name: "val",
+					ParameterType: &bq.QueryParameterType{
+						Type: "TIMESTAMP", // TODO: confirm if DATETIME or TIMESTAMP
+					},
+					ParameterValue: &bq.QueryParameterValue{
+						Value: ts.Format("2006-01-02 15:04:05 MST"),
+					},
+				},
+			},
+			[]Value{ts},
+			ts,
+		},
 	}
 	for _, tc := range testCases {
 		t.Run(tc.name, func(t *testing.T) {
diff --git a/bigquery/params.go b/bigquery/params.go
@@ -29,10 +29,11 @@ import (
 	bq "google.golang.org/api/bigquery/v2"
 )
 
-// See https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#timestamp-type.
+// See https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#timestamp_type.
 var (
-	timestampFormat = "2006-01-02 15:04:05.999999-07:00"
-	dateTimeFormat  = "2006-01-02 15:04:05"
+	timestampFormat  = "2006-01-02 15:04:05.999999-07:00"
+	dateTimeFormat   = "2006-01-02 15:04:05"
+	dateTimeTZFormat = "2006-01-02 15:04:05 MST"
 )
 
 var (
@@ -661,7 +662,7 @@ func convertParamValue(qval *bq.QueryParameterValue, qtype *bq.QueryParameterTyp
 		if isNullScalar(qval) {
 			return NullTimestamp{Valid: false}, nil
 		}
-		formats := []string{timestampFormat, time.RFC3339Nano, dateTimeFormat}
+		formats := []string{timestampFormat, time.RFC3339Nano, dateTimeFormat, dateTimeTZFormat}
 		var lastParseErr error
 		for _, format := range formats {
 			t, err := time.Parse(format, qval.Value)
diff --git a/bigquery/value.go b/bigquery/value.go
@@ -838,6 +838,18 @@ func parseCivilDateTime(s string) (civil.DateTime, error) {
 	return civil.ParseDateTime(parts[0] + "T" + parts[1])
 }
 
+func parseCivilDate(s string) (civil.Date, error) {
+	cdt, err := civil.ParseDate(s)
+	if err != nil {
+		t, err := time.Parse("2006-1-2", s)
+		if err != nil {
+			return civil.Date{}, err
+		}
+		return civil.DateOf(t), err
+	}
+	return cdt, nil
+}
+
 const (
 	// NumericPrecisionDigits is the maximum number of digits in a NUMERIC value.
 	NumericPrecisionDigits = 38
@@ -984,7 +996,7 @@ func convertBasicType(val string, typ FieldType) (Value, error) {
 		}
 		return time.UnixMicro(i).UTC(), nil
 	case DateFieldType:
-		return civil.ParseDate(val)
+		return parseCivilDate(val)
 	case TimeFieldType:
 		return civil.ParseTime(val)
 	case DateTimeFieldType:
