CODEWITHSUNDEEP
google-bigquerytimestamp
Michał Herman
Michał Herman

Reputation: 3587

BigQuery - convert String to Timestamp with default value

In the BigQuery I have String column which contains timestamp data. In the query I want to convert it to the Timestamp column.
I know, that BigQuery have PARSE_TIMESTAMP and TIMESTAMP functions. Issue is, that this data comes from the external source, so I need to be ready for the possibility, that input String may not be correct timestamp.
While using these functions inside the query it will fail, if input data is in the wrong format.
Is there a method to let this query pass even with wrong input data? For example, to change not correct format to null or some arbitrary timestamp like 1900-01-01 00:00:00.
Simple regexp check will not be sufficient. For example 2020-02-29 00:00:00 is a completely fine timestamp, but 2019-02-29 00:00:00 is incorrect.

Upvotes: 0

Views: 2195

Answers (1)

Pentium10
Pentium10

Reputation: 207828

You need to prefix with SAFE in order to get NULLs for invalid data.

SELECT safe.timestamp('2019-02-29 00:00:00') as tt

returns

[
  {
    "tt": null
  }
]

If you begin a function with the SAFE. prefix, it will return NULL instead of an error. The SAFE. prefix only prevents errors from the prefixed function itself: it does not prevent errors that occur while evaluating argument expressions. The SAFE. prefix only prevents errors that occur because of the value of the function inputs, such as "value out of range" errors; other errors, such as internal or system errors, may still occur. If the function does not return an error, SAFE. has no effect on the output. If the function never returns an error, like RAND, then SAFE. has no effect.

https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#safe_prefix

Upvotes: 3

Related Questions