bigQuery not supporting milliseconds timestamps

4
votes

I have a value in my csv file for timetamp as '1522865628160'. When I load the data in bigQuery where this field type is timestamp, it saves the timestamp as '1522865628160000'. so when I query like

select * from <tablename> limit 1

it gives me error

Cannot return an invalid timestamp value of 1522865628160000000 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 timestamp"

please help

1
google-bigquery

1 Answers

5
votes

I think the issue here is that you tried to load your UNIX timestamp data into a timestamp column in BigQuery. 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 the fix here would be to load your data into an INT64 (or INTEGER if you are using legacy) column. From there, you may convert your UNIX timestamp to a bona fide date or timestamp.

There is a MSEC_TO_TIMESTAMP() function which can convert an integer number of milliseconds since the UNIX epoch to a bona fide timestamp, e.g.

SELECT MSEC_TO_TIMESTAMP(1522865628160)

2018-04-04 11:13:48 UTC