PostgreSQL中如何从时间范围提取整小时并计算各小时时长

问题描述 #

我是数据库新手，尤其不熟悉PostgreSQL。现有一张交互记录表，结构如下：

需要编写查询语句，按小时拆分每条记录的时间范围，计算每个小时内的交互时长，期望输出如下：

注：需要覆盖时间范围的所有整小时，例如某记录从17:10开始到19:00结束，需计算17:00、18:00、19:00三个时段的时长。

解决方案 #

以下是实现需求的PostgreSQL查询语句，假设你的表名为interactions：

WITH hourly_intervals AS (
    SELECT
        id_interaction,
        start_time,
        end_time,
        generate_series(
            date_trunc('hour', start_time),
            date_trunc('hour', end_time),
            INTERVAL '1 hour'
        ) AS hour_start
    FROM interactions
)
SELECT
    id_interaction,
    start_time,
    end_time,
    TO_CHAR(hour_start, 'HH24:MI:SS') AS hour,
    TO_CHAR(
        LEAST(hour_start + INTERVAL '1 hour', end_time) - GREATEST(hour_start, start_time),
        'HH24:MI:SS'
    ) AS duration
FROM hourly_intervals
ORDER BY id_interaction, hour_start;

代码解释 #

1. CTE部分（hourly_intervals）

   • date_trunc('hour', start_time)将交互开始时间截断到当前小时的起始点（比如12:40:10变成12:00:00）
   • generate_series函数生成从开始小时到结束小时的所有整小时区间，每条原始交互记录会被拆分成对应小时数的行

2. 主查询部分

   • GREATEST(hour_start, start_time)：确定当前小时内交互的实际开始时间（如果交互开始于小时中间，取交互开始时间；否则取小时起始点）
   • LEAST(hour_start + INTERVAL '1 hour', end_time)：确定当前小时内交互的实际结束时间（如果交互结束于小时中间，取交互结束时间；否则取小时结束点）
   • 两者相减得到当前小时内的交互时长，用TO_CHAR格式化为HH24:MI:SS样式
   • TO_CHAR(hour_start, 'HH24:MI:SS')将小时起始点转换为目标格式

3. 注意事项

   • PostgreSQL中没有24:00:00的时间表示，次日零点会显示为00:00:00，符合标准时间规范
   • 请将语句中的interactions替换为你的实际表名

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