英文:
Find data between dates using BigQuery
问题
我有一个BigQuery表,其中一列名为timestamp,它包含一个字符串时间值(Fri Jun 03 16:04:53 UTC 2022)。
我想创建一个BigQuery查询,返回两个日期之间的值(08/07/2023和08/08/2023)。
在BigQuery中,你可以这样做:
SELECT type, COUNT(type)
FROM `event`
WHERE TIMESTAMP(timestamp) BETWEEN TIMESTAMP('2023-08-07') AND TIMESTAMP('2023-08-08')
GROUP BY type
这个查询将返回在指定日期范围内的每种类型的计数。请注意,你需要将日期格式转换为BigQuery支持的格式('YYYY-MM-DD')并使用TIMESTAMP
函数将字符串时间值转换为时间戳。
英文:
I have a bigquery table where a column is name timestamp and it contains a string time value(Fri Jun 03 16:04:53 UTC 2022)
I would like to create a bigQuery query to return value between 2 dates (08/07/2023 and 08/08/2023)
How can I do this in BigQuery
here is part of my query
** SELECT type,count(type) FROM event
group by type**
答案1
得分: 2
我们可以使用PARSE_TIMESTAMP
将字符串转换为TIMESTAMP
值。
示例:
SELECT DATE(PARSE_TIMESTAMP('%a %b %d %k:%M:%S UTC %Y','Fri Jun 03 16:04:53 UTC 2022'))
-- 2022-06-03
选择查询可能如下所示:
SELECT *
FROM table
WHERE
DATE(PARSE_TIMESTAMP('%a %b %d %k:%M:%S UTC %Y', timestamp)) BETWEEN '2022-08-07' AND '2022-08-08'
英文:
We can use PARSE_TIMESTAMP
to convert a string into a TIMESTAMP
value.
Example:
SELECT DATE(PARSE_TIMESTAMP('%a %b %d %k:%M:%S UTC %Y','Fri Jun 03 16:04:53 UTC 2022'))
-- 2022-06-03
The select query would look something like:
SELECT *
FROM table
WHERE
DATE(PARSE_TIMESTAMP('%a %b %d %k:%M:%S UTC %Y', timestamp)) BETWEEN '2022-08-07' AND '2022-08-08'
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论