Cannot DELETE or UPDATE row in Google BigQuery due to a timestamp for a field being outside the range of Standard SQL timestamp

Question

In Google BigQuery, I have a timestamp field which has the year 20195. This is ofcourse causing errors as it's outside the Standard SQL Timestamp range. However I cannot update this records or delete this record as the error, even by using SAFE_CAST. For example, i've tried:

UPDATE [table] SET DateField = SAFE_CAST('2019-01-01 00:00:00 UTC' AS TIMESTAMP)...

"Cannot return an invalid timestamp value of 575129030400000000 microseconds relative to the Unix epoch." pops up. I know how to SELECT and return a null instead of an error using Safe-Cast, but cannot use this to update/delete.

Question is: How can I delete or update this field successfully with out any errors?

Answer 1

You can use the safe. prefix to return NULL instead:

select safe.timestamp_micros(575129030400000000)

Answer 2

You can convert to microseconds (which doesn't result in a bounds check) and then attempt to convert back, deleting the row if the safe conversion results in NULL:

DELETE dataset.table
WHERE ts IS NOT NULL
  AND SAFE.TIMESTAMP_MICROS(UNIX_MICROS(ts)) IS NULL

Or if you want to set such values to NULL instead:

UPDATE dataset.table
SET ts = SAFE.TIMESTAMP_MICROS(UNIX_MICROS(ts))
WHERE true

Answer 3

If you want to repair bad values using UPDATE you have to apply it across the whole table, using a conditional. Elliot's answer is correct but I can't can't comment or upvote so I'll elaborate slightly and answer this question:

UPDATE `my.table` 
SET DateField = IF(SAFE.DATE(DateField) IS NULL AND DateField IS NOT NULL, TIMESTAMP('2019-01-01 00:00:00 UTC'), DateField)
WHERE true

The WHERE true is the secret sauce, which isn't obvious from Elliot's answer. If you try to do WHERE id = 1234 or whatever it will continue to give the same error. SAFE.DATE is a concise way of checking for valid dates and will return NULL if they are invalid, however your field may be nullable which is why I have added the null check.