bigquery: failing to parse some BQ query parameter dates and datetimes

[ash-ddog]

Client

cloud.google.com/go/bigquery v1.64.0

Environment

go 1.24.1

Code and Dependencies

Stack trace (this is specifically for dates, but probably same area for datetime)

civil.ParseDate (civil.go:50) cloud.google.com/go/civil
bigquery.convertBasicType (value.go:987) cloud.google.com/go/bigquery
bigquery.convertParamValue (params.go:703) cloud.google.com/go/bigquery
bigquery.baToQueryParameter (params.go:602) cloud.google.com/go/bigquery
bigquery.bqToQueryConfig (query.go:286) cloud.google.com/go/bigquery
bigquery.bqToJobConfig (job.go:157) cloud.google.com/go/bigquery
bigquery. (*Job).Config (job.go:134) cloud.google.com/go/bigquery

Expected behavior

Can retrieve a job config via job.Confg() without error

Actual behavior

job.Config() returns an error when trying to parse a BQ query param value of type DATE/DATETIME

Additional context

Saw this older issue but has been closed and not exactly sure it's related: #6409

I see two classes of error

1. Parsing date: dates are in format YYYY-M-D but trying to be parsed as YYYY-MM-DD (i.e. civil.ParseDate)

parsing time "2021-10-1" as "2006-01-02": cannot parse "1" as "02"
parsing time "2022-1-1" as "2006-01-02": cannot parse "1-1" as "01"
parsing time "2024-1-21" as "2006-01-02": cannot parse "1-21" as "01"
parsing time "2024-8-1" as "2006-01-02": cannot parse "8-1" as "01"
parsing time "2024-11-7" as "2006-01-02": cannot parse "7" as "02"
parsing time "2025-1-1" as "2006-01-02": cannot parse "1-1" as "01"
parsing time "2025-2-21" as "2006-01-02": cannot parse "2-21" as "01"
parsing time "2025-3-3" as "2006-01-02": cannot parse "3-3" as "01"
parsing time "2025-3-5" as "2006-01-02": cannot parse "3-5" as "01"
parsing time "2025-3-8" as "2006-01-02": cannot parse "3-8" as "01"
parsing time "2025-3-12" as "2006-01-02": cannot parse "3-12" as "01"
parsing time "2025-3-16" as "2006-01-02": cannot parse "3-16" as "01"
parsing time "2025-3-17" as "2006-01-02": cannot parse "3-17" as "01"
parsing time "2025-3-18" as "2006-01-02": cannot parse "3-18" as "01"
parsing time "2025-3-19" as "2006-01-02": cannot parse "3-19" as "01"
parsing time "2025-3-21" as "2006-01-02": cannot parse "3-21" as "01"

2. Parsing datetime: datetime is in format YYYY-MM-DD HH:MM:SS UTC but trying to parse as YYYY-MM-DDTHH:MM:SS[.FFFFFFFFF] (i.e. civil.ParseDateTime)

parsing time "2025-03-19 07:00:00 UTC": extra text: " UTC"
parsing time "2025-03-19 08:00:00 UTC": extra text: " UTC"
parsing time "2025-03-19 09:00:00 UTC": extra text: " UTC"
parsing time "2025-03-19 10:00:00 UTC": extra text: " UTC"
parsing time "2025-03-19 11:00:00 UTC": extra text: " UTC"
parsing time "2025-03-19 12:00:00 UTC": extra text: " UTC"
parsing time "2025-03-19 13:00:00 UTC": extra text: " UTC"
parsing time "2025-03-19 14:00:00 UTC": extra text: " UTC"

Also for context: These errors are occurring in GCP projects we query on behalf of our customers, so we don't actually have access to these projects to look at the details of these jobs (i.e. I do not/can not know how these jobs end up with the query parameter values).

Please let me know if I can provide additional info. Also happy to try to open a PR (but I would probably just stop using civil.ParseDate and civil.ParseDateTime).

[alvarowolfx]

hey @ash-ddog, thanks for the report. This issue is actually similar to #6651, which at time was solved by #6653, but we might need to improve coverage with other datetime formats as you just found out. I'll evaluate here what we can do to improve parsing to solve the issue that you reported and will keep you posted as I open PRs to potentially fix the issue.

[alvarowolfx]

@ash-ddog for the first case I managed to reproduce here. Can you confirm if for the second case, the data type is DATETIME or TIMESTAMP ? I tried to create a job with 2020-10-15 15:04:05 UTC as query parameter value of type DATETIME and BQ throws an 400 error, it only works with TIMESTAMP. For TIMESTAMP I managed to reproduce and fix the issue, but could not repro with DATETIME.

With DATETIME

$ bq query \              
   --use_legacy_sql=false \
   --parameter='dt_value:DATETIME:2020-10-15 15:04:05 UTC' \
   'SELECT @dt_value'
BigQuery error in query operation: Error processing job 'REDACTED:bqjob_r5f79fbb4fa0063e0_00000195ed88319b_1': Unparseable query parameter `dt_value` in type `TYPE_DATETIME`,
Invalid datetime string "2020-10-15 15:04:05 UTC" value: '2020-10-15 15:04:05 UTC'
Failure details:
- Unparseable query parameter `dt_value` in type `TYPE_DATETIME`,
Invalid datetime string "2020-10-15 15:04:05 UTC" value:

With TIMESTAMP

$  bq query \
   --use_legacy_sql=false \
   --parameter='ts_value:TIMESTAMP:2020-10-15 15:04:05 UTC' \
   'SELECT @ts_value'
+---------------------+
|         f0_         |
+---------------------+
| 2020-10-15 15:04:05 |
+---------------------+

[alvarowolfx]

Work in progress PR with repro scenarios for the cases that were reported #11932. Still need confirmation on the second scenario, as I could not reproduce with DATETIME

[ash-ddog]

Thanks for the responses @alvarowolfx and thanks for taking a look.

Can you confirm if for the second case, the data type is DATETIME or TIMESTAMP ?

Ack, let me try to get more info and I will post back as soon as I can. 🙏

[ash-ddog]

Looks like the error is occurring in convertParamValue when qtype.Type is TIMESTAMP, stack trace (v1.64.0)

bigquery.convertParamValue (params.go:674) cloud.google.com/go/bigquery
bigquery.bqToQueryParameter (params.go:602) cloud.google.com/go/bigquery
bigquery.bqToQueryConfig (query.go:286) cloud.google.com/go/bigquery
bigquery.bqToJobConfig (job.go:157) cloud.google.com/go/bigquery
bigquery. (*Job).Config (job.go:134) cloud.google.com/go/bigquery

and as an example, qval.Value = "2025-04-01 16:00:00 UTC"

[alvarowolfx]

awesome @ash-ddog , so with TIMESTAMP I can repro the issue indeed and is solved by #11932. I'll break it down between some improvements on the civil package and the bigquery specific fixes