bigQuery 不支持毫秒时间戳 [英] bigQuery not supporting milliseconds timestamps

问题描述

我的 csv 文件中有一个时间戳值为1522865628160".当我在 bigQuery 中加载此字段类型为时间戳的数据时，它将时间戳保存为1522865628160000".所以当我查询时

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

它给了我错误

无法返回相对于 Unix 纪元 1522865628160000000 微秒的无效时间戳值.有效时间戳值范围为 [0001-01-1 00:00:00, 9999-12-31 23:59:59.999999]；写入字段时间戳错误"

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"

请帮忙

推荐答案

我认为这里的问题是您试图将 UNIX 时间戳数据加载到 BigQuery 的 timestamp 列中.BigQuery 时间戳列与 UNIX 时间戳不同.后者只是一个数值，表示自 1970 年 UNIX 时代开始以来的秒数.

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.

因此，此处的解决方法是将您的数据加载到 INT64(或 INTEGER，如果您使用的是旧版)列.从那里，您可以将您的 UNIX 时间戳转换为真正的日期或时间戳.

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.

有一个 MSEC_TO_TIMESTAMP() 函数，它可以将自 UNIX 纪元以来的整数毫秒数转换为真正的时间戳，例如

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

这篇关于bigQuery 不支持毫秒时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！