如何使用BigQuery查询指定日期范围内的数据（字符串Timestamp列）

你需要先把字符串格式的时间转换为BigQuery可识别的时间类型，再添加时间范围筛选条件，结合你的现有查询，完整语句如下：

SELECT type, COUNT(type)
FROM `event`
WHERE PARSE_TIMESTAMP("%a %b %d %H:%M:%S %Z %Y", timestamp) 
      BETWEEN TIMESTAMP("2023-08-07 00:00:00 UTC") 
      AND TIMESTAMP("2023-08-08 23:59:59 UTC")
GROUP BY type

关键部分说明 #

• PARSE_TIMESTAMP函数：将字符串格式的时间转换为BigQuery的TIMESTAMP类型，"%a %b %d %H:%M:%S %Z %Y"是匹配你给出的时间格式（Fri Jun 03 16:04:53 UTC 2022）的格式符：
  • %a：缩写星期几（如Fri）
  • %b：缩写月份（如Jun）
  • %d：两位日期（如03）
  • %H:%M:%S：时分秒（如16:04:53）
  • %Z：时区标识（如UTC）
  • %Y：四位年份（如2022）
• 时间范围筛选：使用BETWEEN指定2023年8月7日0点到8月8日23:59:59的UTC时间范围，确保包含这两天的所有数据。如果想更严谨，也可以用>= TIMESTAMP("2023-08-07") AND < TIMESTAMP("2023-08-09")，避免漏过8月8日23:59:59之后到次日0点之间的毫秒级数据。

可选优化 #

如果需要频繁按时间筛选，建议在表中新增一个持久化的TIMESTAMP类型列，避免每次查询重复转换字符串，提升性能：

ALTER TABLE `event`
ADD COLUMN parsed_timestamp TIMESTAMP 
AS PARSE_TIMESTAMP("%a %b %d %H:%M:%S %Z %Y", timestamp) STORED;

之后查询可直接用该列筛选：

SELECT type, COUNT(type)
FROM `event`
WHERE parsed_timestamp BETWEEN TIMESTAMP("2023-08-07") AND TIMESTAMP("2023-08-08 23:59:59 UTC")
GROUP BY type

内容的提问来源于stack exchange，提问作者user2570135