本文介绍了bigQuery不支持毫秒时间戳的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

08-04 16:58
查看更多