bigquery standard sql error, invalid timestamp

1
votes

I'm playing with some tables in bigquery and I receive this error:

Cannot return an invalid timestamp value of -62169990264000000 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]

Doing the query in legacy sql and sorting ascending, it displays as 0001-11-29 22:15:36 UTC

How does it get transformed into microseconds?

This is the query:

#standardSQL
SELECT
  birthdate
FROM
  X
WHERE
  birthdate IS NOT NULL
ORDER BY
  birthdate ASC
2
google-bigquery
show your query that produces error - Mikhail Berlyant
changed the details, added now - Alexandru R

2 Answers

2
votes

Midnight of January 1 of the year 0001 (the minimum possible timestamp value in standard SQL) is -62135596800000000 in microseconds relative to the UNIX epoch, which is greater than -62169990264000000. I don't have a good explanation for legacy SQL's behavior with that timestamp value, but you can read about some suggestions for dealing with it in standard SQL in this item on the issue tracker. We plan to add some content to the migration guide about this timestamp behavior in the future as well.

2
votes

**strong text**Confirming , that in BigQuery Legacy SQL 

SELECT USEC_TO_TIMESTAMP(-62169990264000000)

produces 0001-11-29 22:15:36 UTC timestamp

whereas in BigQuery Standard SQL 

SELECT TIMESTAMP_MICROS(-62169990264000000)

produces error: TIMESTAMP value is out of allowed range: from 0001-01-01 00:00:00.000000+00 to 9999-12-31 23:59:59.999999+00.

How does it get transformed in microseconds?

TIMESTAMP
You can describe TIMESTAMP data types as either UNIX timestamps or calendar datetimes. BigQuery stores TIMESTAMP data internally as a UNIX timestamp with microsecond precision.

See more about TIMESTAMP type