BigQuery Error: Cannot return an invalid timestamp value of 6328502092800000000 microseconds relative to the Unix epoch

2
votes

We are using simple select query with Standard SQL in BigQuery.

select expiration_date FROM cards

But, It returns an below error,

Error: Cannot return an invalid timestamp value of 6328502092800000000 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];

Could anyone help me for the same?

2
google-bigquerybigquery-standard-sql
What part is unclear? that value is out of the range (being 200675 years after epoch start) so is not representable as a four digit year date. We don't know who put it into your database. - Pete Kirkham
Yes, Those data in our database. But, We don't need to change, If we will use Legacy SQL then it is working fine. How can we use standard query for the same? - Jimesh Gajera

2 Answers

3
votes

The issue you are experiencing is that Standard SQL and Legacy SQL have different definitions for the TIMESTAMP data type. In fact, Standard SQL has a stricter range of valid TIMESTAMP values, being 253402300799999999 the maximum value and -62135596800000000 the minimum (take into account that your value, 6328502092800000000 is higher than the maximum allowed).

For reference, here you have the TIMESTAMP definitions for both SQL languages:

The migration guide from Legacy SQL to Standrad SQL offers a nice guide on how to correct the invalid timestamp value errors you are facing. The main two approaches suggested are the ones below, but please visit the documentation in order to obtain detailed information about each of them:

  1. Use a UDF to filter invalid timestamps.
  2. use SAFE_CAST with the timestamp column in order to return NULL values instead of an error.
2
votes

This means the TIMESTAMP stored in your BigQuery table has the numerical value 6328502092800000000.

This numerical value is intended to represent the number of micro-seconds since the start of the Unix epoch (1st Jan 1970, 00:00). If you do the calculation, this is a date over 200,000 years in the future; the error message is telling you that dates from the year 10,000 A.D. onwards are not considered valid.

Looking at your value, it seems likely to me that something has gone awry and you are actually representing nanoseconds since the start of some epoch - the unmodified value should not have been uploaded to BigQuery as a TIMESTAMP. This may be the fault of the client library which you are using to upload data to BigQuery, or of the code that passes data to it.

If we simply assume your value is 'nanoseconds since the start of the Unix epoch', we get a timestamp on 17th Jul 2170 - possibly what you wanted, if you were expecting dates in the future to be stored. Alternatively it may be that we have the wrong epoch (in other words, perhaps you are counting nanoseconds from a different starting point?). In that case you would need to determine the correct epoch (it depends where the data came from!).