Find data between dates using BigQuery

huangapple go评论72阅读模式
英文:

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'

huangapple
  • 本文由 发表于 2023年8月9日 04:44:49
  • 转载请务必保留本文链接:https://go.coder-hub.com/76863097.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定