PostgreSQL时间戳截断结果异常：两种日聚合查询输出差异分析

我有一张名为device_data的表，结构如下：

Column      |            Type             | Collation | Nullable | Default 
------------------+-----------------------------+-----------+----------+---------
 id               | integer                     |           |          | 
 date             | timestamp without time zone |           |          | 
 upload           | real                        |           |          | 
 download         | real                        |           |          | 

使用第一种查询进行日维度数据截断聚合：

查询1 #

SELECT
  date_trunc('day', date) as daily,
  avg(upload) as avg_upload,
  avg(download) as avg_download
FROM device_info
WHERE
  date BETWEEN '2022-10-07 10:28:46' AND '2022-11-06 10:28:46'
GROUP BY daily
ORDER BY daily;

结果首行为：

2022-10-07 00:00:00 | 41.691493286006484 | 41.571846902122246

而我认为功能相同的第二种查询：

查询2 #

SELECT
  date_trunc('month', date) + date_part('day', date)::int / 1 * interval '1 day' AS daily,
  avg(upload) as avg_upload,
  avg(download) as avg_download
FROM device_info
WHERE
  date BETWEEN '2022-10-07 10:28:46' and '2022-11-06 10:28:46'
GROUP BY daily
ORDER BY daily ASC;

结果首行却偏移了一天：

2022-10-08 00:00:00 | 41.691493286006484 | 41.571846902122246

请问第二种查询的问题出在哪里？我需要该方式来实现周维度及6天维度的数据截断。

查询2的问题根源 #

问题出在date_part('day', date)的返回逻辑：这个函数返回的是当月的第N天（从1开始计数），而date_trunc('month', date)得到的是当月第一天的0点，两者直接相加后，相当于把日期变成了当月第1天 + N天，这就比实际日期多了1天。

举个具体例子：

• 对于2022-10-07 10:28:46，date_trunc('month', date)得到2022-10-01 00:00:00
• date_part('day', date)返回7
• 相加后结果为2022-10-01 + 7天 = 2022-10-08 00:00:00，自然产生了一天的偏移。

正确的计算应该给日数减1，修正后表达式为：
date_trunc('month', date) + (date_part('day', date)::int - 1) * interval '1 day'

通用自定义维度截断方法 #

如果需要实现周维度（自定义起始日）、6天维度这类非标准截断，可以基于基准日期做模运算偏移：

1. 6天维度聚合 #

以查询起始日2022-10-07为基准，每6天为一组：

SELECT
  -- 计算每个日期所属的6天区间起始
  date_trunc('day', date) - ((date_trunc('day', date) - '2022-10-07'::date)::int % 6) * interval '1 day' AS six_day_period,
  avg(upload) as avg_upload,
  avg(download) as avg_download
FROM device_info
WHERE
  date BETWEEN '2022-10-07 10:28:46' AND '2022-11-06 10:28:46'
GROUP BY six_day_period
ORDER BY six_day_period;

2. 自定义周维度（以指定日期为周起始） #

如果需要自定义一周的起始（比如以2022-10-07为第一周第一天），写法类似：

SELECT
  date_trunc('day', date) - ((date_trunc('day', date) - '2022-10-07'::date)::int % 7) * interval '1 day' AS custom_week,
  avg(upload) as avg_upload,
  avg(download) as avg_download
FROM device_info
WHERE
  date BETWEEN '2022-10-07 10:28:46' AND '2022-11-06 10:28:46'
GROUP BY custom_week
ORDER BY custom_week;

修正后的日维度查询2 #

如果坚持用原思路实现日维度，修正后的完整查询：

SELECT
  date_trunc('month', date) + (date_part('day', date)::int - 1) * interval '1 day' AS daily,
  avg(upload) as avg_upload,
  avg(download) as avg_download
FROM device_info
WHERE
  date BETWEEN '2022-10-07 10:28:46' and '2022-11-06 10:28:46'
GROUP BY daily
ORDER BY daily ASC;

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