CODEWITHSUNDEEP
google-bigquery
jStaff
jStaff

Reputation: 710

What format does BigQuery timestamp take?

I am trying to input a timestamp type into BigQuery as a RFC3339 string:

"2019-07-25T11:07:41-04:00"

It doesn't seem to be working. What format does Timestamp type expect in BigQuery? The documentation doesn't specify input.

Upvotes: 1

Views: 3007

Answers (2)

Mohammed Sherif KK
Mohammed Sherif KK

Reputation: 666

enter image description here

**Your input works fine for me. **

Upvotes: 1

justbeez
justbeez

Reputation: 1387

That is a valid format for casting or coercion, but be mindful that leading and trailing whitespace (or whitespace before the timezone offset, which many systems add) will cause the coercion to fail.

I would suggest something along the lines of (using TIMESTAMP()):

TIMESTAMP(TRIM(ts_string))

. . . or using CAST():

CAST(TRIM(ts_string) AS TIMESTAMP)

If there's whitespace within the string, you could just replace it using REPLACE():

TIMESTAMP(REPLACE(ts_string," ",""))

. . . or more thoroughly, replace all types of whitespace including tab using REGEXP_REPLACE():

TIMESTAMP(REGEXP_REPLACE(" 2019-07-25T11:07:41 -04:00 ",r"\s+",""))

And see if that works for you. It may also be worth looking at the value in a hex or text editor where you can see if there are any hidden characters in the string.

If there are things you need to work around that don't play nicely with simple replacements, you can use PARSE_TIMESTAMP() and provide a pattern mapping the elements of the source string to a valid TIMESTAMP.

If you're still having issues, it would also help to know how you're trying to load this string—is this via an import/load job, or a SQL INSERT or UPDATE? Are you using the BQ console, API, bq command line tool, or a language-specific SDK? What's the source format of the data? All of these may be helpful clarifications on the question to get you a better answer. What error message are you being shown, and is it for a single value or all values?

It's possible that some of the values are in an invalid format and are causing the operation to fail. You could test this by importing a few rows of the data you know are in a valid format and seeing if you still get an error.

Upvotes: 1

Related Questions