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屋！