在BigQuery中按日期与小时对timestamp时间戳数据进行分组

核心实现逻辑 #

直接使用BigQuery内置时间处理函数即可完成需求，不需要复杂的字符串切割：

• 日期字段：用DATE()函数直接从timestamp中提取日期部分
• 整小时时间戳：用TIMESTAMP_TRUNC()函数指定截断粒度为HOUR，直接输出整小时的timestamp值

示例代码 #

假设你的时间戳字段名为ts，来源表为your_project.your_dataset.your_table，查询语句如下：

SELECT
  DATE(ts) AS dt,
  TIMESTAMP_TRUNC(ts, HOUR) AS hour_start,
  -- 此处补充你需要聚合的指标，例如：
  COUNT(DISTINCT user_id) AS uv
FROM `your_project.your_dataset.your_table`
-- 按两个维度分组直接引用字段别名即可
GROUP BY dt, hour_start
ORDER BY dt DESC, hour_start DESC

以你给出的示例值2021-08-30 22:10:22.838 UTC作为输入，上述语句返回的dt值为2021-08-30，hour_start值为2021-08-30 22:00:00 UTC，完全符合输出要求。

可选扩展：指定时区拆分 #

如果需要按非UTC时区拆分日期和小时，直接在函数中追加时区参数即可（推荐使用IANA时区名称，避免夏令时异常），示例为按东八区拆分：

SELECT
  DATE(ts, "Asia/Shanghai") AS dt_cst,
  TIMESTAMP_TRUNC(ts, HOUR, "Asia/Shanghai") AS hour_start_cst
FROM `your_project.your_dataset.your_table`

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