Updating invalid timestamps in Google BigQuery table

Question

My table has a few invalid timestamps that I think are too precise (maybe beyond microseconds) for BigQuery.

When I tried updating the table with the following query using Standard SQL:

UPDATE mytable
SET event_time = TIMESTAMP(DATETIME(TIMESTAMP_MILLIS(CAST(SUBSTR(SAFE_CAST(UNIX_MILLIS(event_time) AS string),1,13) AS int64))))
WHERE DATE(logtime) BETWEEN "2018-03-21" AND "2018-03-23"
    AND event_time IS NOT NULL

I get the invalid timestamp error:

Cannot return an invalid timestamp value of 1521738691071000064 microseconds relative to the Unix epoch. The range of valid timestamp values is [0001-01-1 00:00:00, 9999-12-31 23:59:59.999999]; error in writing field event_time

I think the problem is the SET event_time = part, but I don't know how to get around setting the values in the event_time column without referring to it.

Anyone have any ideas on how to resolve this?

Sample data and desired results would help. What does "invalid timestamp" mean? Obviously if the data is being stored in a column whose datatype is timestamp, then BigQuery doesn't think the values are invalid. — Gordon Linoff
I can't provide sample data from the event_time field, because simply querying its raw data returns the same invalid timestamp error. Data is only returned if I transform it using the timestamp transformation I used in my update query. However, my desired results would look something like this: 2018-03-23 21:06:46.116 UTC — dnaeye
Can you post the results of SELECT TO_JSON_STRING(a) FROM your_table a LIMIT 1 — Felipe Hoffa
Felipe, when I try to run the query, I get the following error: Invalid timestamp value: 1521749062783000064 — dnaeye
Please file a ticket in the BigQuery issue tracker - support might be able to fix it. In the meantime: Try running queries with #legacySQL, that might work — Felipe Hoffa

dnaeye dnaeye · Accepted Answer · 2019-12-24T19:26:57

Necessity is the mother of invention. For anyone who has a similar issue, I've figured out a workaround.

Create a new table of the affected rows (include this in the WHERE clause: LENGTH(CAST(UNIX_MILLIS(event_time) as string)) > 13, while transforming the invalid timestamp into a valid format using TIMESTAMP(DATETIME(TIMESTAMP_MILLIS(CAST(SUBSTR(SAFE_CAST(UNIX_MILLIS(event_time) AS string),1,13) AS int64))))
Delete the affected rows from the original table using the WHERE clause mentioned above.
Insert all rows from the new table back into the original table.

A lot more work, but it should do the trick.

Updating invalid timestamps in Google BigQuery table

1 Answers