Solved: Bigquery - Cannot return an invalid timestamp value error in writing field timestamp

While writing the milliseconds '1522865628160' to BigQuery table, It fails with the below error:

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

Solution

A BigQuery timestamp column is not the same thing as a UNIX timestamp. The latter is just a numerical value representing the number of seconds since the start of the UNIX epoch in 1970.

So, before you load the data to a BigQuery table into an INT64 Column and then while retrieval you can use the below function to convert milliseconds to date:

SELECT TIMESTAMP_MILLIS(1522865628160) \n2018-04-04 18:13:48.160 UTC\n

dan-irving

posted on