PostgreSQL如何按日期分组并截断时间戳？

嘿，这个需求在PostgreSQL里其实有好几种简单好用的实现方式，我给你一步步讲清楚：

核心思路 #

不管用哪种方法，本质都是把带时分秒的时间字段转换/截断到“日期”维度，然后基于这个日期维度做分组聚合。

方法1：直接类型转换（最简洁） #

PostgreSQL支持直接把timestamp或timestamptz类型的字段转换成date类型，自动截断时分秒部分：

-- 示例：按日期统计记录数
SELECT
  your_time_column::date AS group_date,
  COUNT(*) AS record_count
FROM your_table
GROUP BY group_date
ORDER BY group_date;

你也可以用DATE()函数来实现同样的效果，写法更直观：

SELECT
  DATE(your_time_column) AS group_date,
  SUM(your_value_column) AS total_value
FROM your_table
GROUP BY DATE(your_time_column)
ORDER BY group_date;

方法2：使用TRUNC()函数（灵活适配不同粒度） #

如果之后需要调整截断粒度（比如按周、月分组），TRUNC()函数会更灵活。针对日期分组，我们可以指定截断到'day'：

SELECT
  TRUNC(your_time_column, 'day') AS group_date,
  AVG(your_numeric_column) AS avg_value
FROM your_table
GROUP BY TRUNC(your_time_column, 'day')
ORDER BY group_date;

注意：TRUNC()返回的还是timestamp类型（比如2024-05-20 00:00:00），但分组效果和纯日期是一致的。

时区注意事项（针对timestamptz类型） #

如果你的时间字段是带时区的timestamptz，要注意时区对日期的影响。比如想按上海时区的日期分组，需要先转换时区再截断：

SELECT
  (your_time_column AT TIME ZONE 'Asia/Shanghai')::date AS shanghai_date,
  COUNT(*) AS record_count
FROM your_table
GROUP BY shanghai_date
ORDER BY shanghai_date;

适配你的需求 #

你可以把上面示例里的your_time_column、your_table换成你实际的字段名和表名，再根据需要调整聚合函数（COUNT/SUM/AVG等），就能得到你期望的分组结果啦。

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