PostgreSQL如何查询指定时间范围内带时区的每小时分组结果

基础查询（返回day、hour字段列表） #

如果需要生成指定日期完整的24小时连续序列（无数据的小时也返回），可以使用PostgreSQL内置的generate_series函数生成时间序列，同时通过AT TIME ZONE语法指定时区：

SELECT
  -- 按目标时区转换后提取日期
  DATE(series_hour AT TIME ZONE 'Asia/Shanghai') AS day,
  -- 按目标时区转换后提取小时，转成整数类型
  EXTRACT(HOUR FROM series_hour AT TIME ZONE 'Asia/Shanghai')::INT AS hour
FROM
  generate_series(
    -- 起始时间显式指定时区，此处+08代表东八区
    '2021-08-01 00:00:00+08'::timestamptz,
    -- 结束时间到当日23点即可，步长为1小时
    '2021-08-01 23:00:00+08'::timestamptz,
    '1 hour'::interval
  ) AS t(series_hour)
ORDER BY hour;

如果你是从现有业务表中按小时聚合查询，替换FROM部分的生成序列逻辑为你的业务表即可，示例如下：

SELECT
  DATE(target_datetime AT TIME ZONE 'Asia/Shanghai') AS day,
  EXTRACT(HOUR FROM target_datetime AT TIME ZONE 'Asia/Shanghai')::INT AS hour
FROM your_business_table
WHERE
  -- 时间范围也显式指定时区避免解析错误
  target_datetime BETWEEN '2021-08-01 00:00:00+08' AND '2021-08-01 23:59:59+08'
-- 按小时去重聚合
GROUP BY 1,2
ORDER BY 1,2;

注意：将代码中的Asia/Shanghai替换为你实际需要的时区即可，也可以用时区偏移量如UTC+8代替。

直接返回指定格式的JSON结果 #

使用PostgreSQL的JSON聚合函数可以直接输出符合要求的JSON数组：

SELECT json_agg(row_to_json(t)) AS result
FROM (
  SELECT
    DATE(series_hour AT TIME ZONE 'Asia/Shanghai')::TEXT AS day,
    EXTRACT(HOUR FROM series_hour AT TIME ZONE 'Asia/Shanghai')::INT AS hour
  FROM
    generate_series(
      '2021-08-01 00:00:00+08'::timestamptz,
      '2021-08-01 23:00:00+08'::timestamptz,
      '1 hour'::interval
    ) AS t(series_hour)
) t;

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