CODEWITHSUNDEEP
google-bigquery
Alexandru R
Alexandru R

Reputation: 8823

bigquery standard sql error, invalid timestamp

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

Upvotes: 2

Views: 24232

Answers (3)

Yev Guyduy
Yev Guyduy

Reputation: 1549

Came across a similar issue using TIMESTAMP_MILLIS(unix_timestamp_column) giving me the following exception:

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.


Turned out that unix_timestamp_column had mixed data where at one point timestamp was stored in microseconds and then in milliseconds... 1 millisecond = 1000 microseconds...


TIMESTAMP_MILLIS(1698425168223000) ~= 53,875 years... Convert to today = UNIX epoch [01/01/1970] + 53,875 years which is > 9999-12-31 23:59:59.999999+00.

So I used

select
IFNULL(SAFE.TIMESTAMP_MILLIS(1698425168223000),TIMESTAMP_MICROS(1698425168223000))

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

**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

Upvotes: 3

Elliott Brossard
Elliott Brossard

Reputation: 33705

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.

Upvotes: 2

Related Questions