问题描述
我的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时间戳数据加载到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不支持毫秒时间戳的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!