在BigQuery中获取不超过一个小时的数据 [英] Get data that is no more than an hour old in BigQuery

问题描述

尝试使用以下语句:

SELECT * 
FROM data.example 
WHERE TIMESTAMP(timeCollected) < DATE_ADD(USEC_TO_TIMESTAMP(NOW()), 60, 'MINUTE') 

从我的bigquery数据中获取数据.即使时间不在该范围内，它似乎也会返回相同的结果集. timeCollected的格式为2015-10-29 16:05:06.

to get data from my bigquery data. It seems to return same set of result even when time is not within the range. timeCollected is of the format 2015-10-29 16:05:06.

我正在尝试建立一个查询，该查询旨在返回不超过一个小时的数据.因此，应该返回在过去一小时内收集到的数据，其余的应忽略.

I'm trying to build a query that is meant to return is data that is not older than an hour. So data collected within the last hour should be returned, the rest should be ignored.

推荐答案

您所做的查询的意思是将收集时间小于一个小时的任何东西归还给我"，这实际上意味着您的整个表格.您需要以下内容(至少从我的评论中得出):

The query you made means "return to me anything that has a collection time smaller than an hour in the future" which will literally mean your whole table. You want the following (from what I got through your comment, at least) :

SELECT * 
FROM data.example 
WHERE TIMESTAMP(timeCollected) > DATE_ADD(USEC_TO_TIMESTAMP(NOW()), -60, 'MINUTE')

这意味着任何不超过一个小时前的timeCollected都不会返回.我相信这就是您想要的.

This means that any timeCollected that is NOT greater than an hour ago will not be returned. I believe this is what you want.

此外，除非需要，否则Select *在BigQuery中也不理想.由于数据是按列保存的，因此您可以通过仅选择所需的内容来省钱.我不知道您的用例，因此*可能值得保证

Also, unless you need it, Select * is not ideal in BigQuery. Since the data is saved by column, you can save money by selecting only what you need down the line. I don't know your use case, so * may be warranted though

这篇关于在BigQuery中获取不超过一个小时的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！