PostgreSQL 13中基于同一列时间计算每日有效时长的方法

假设你的history表结构如下：

CREATE TABLE history (
    date DATE,
    time TIME
);

可以通过以下SQL查询每日有效时长：

WITH ranked_times AS (
    SELECT
        date,
        time,
        ROW_NUMBER() OVER (PARTITION BY date ORDER BY time) AS rn
    FROM history
),
time_pairs AS (
    SELECT
        r1.date,
        r1.time AS start_time,
        r2.time AS end_time
    FROM ranked_times r1
    JOIN ranked_times r2
        ON r1.date = r2.date
        AND r2.rn = r1.rn + 1
    WHERE MOD(r1.rn, 2) = 1
)
SELECT
    date,
    SUM(EXTRACT(EPOCH FROM (end_time - start_time)) / 3600) AS effective_hours,
    SUM(end_time - start_time) AS effective_interval
FROM time_pairs
GROUP BY date
ORDER BY date;

代码说明： #

1. ranked_times 临时表：按date分组，对每组内的time按升序排序，给每条记录分配行号rn。这样每天的时间会被按顺序标记为1、2、3、4...
2. time_pairs 临时表：将行号为奇数的记录（工作时段开始时间）和下一行（行号+1，对应时段结束时间）配对，确保每对都是一段有效工作时段的起止。
3. 最终查询：按日期分组，计算每对时间的差值总和。可以选择以小时为单位的数值（effective_hours），或者直接保留原生时间间隔类型（effective_interval）。

注意事项： #

• 确保每天的时间记录成对出现（比如上班、午休开始、午休结束、下班，共4条形成两对），如果某天有奇数条记录，最后一条无法配对会被忽略。
• 若需处理异常数据（如某天记录数量不对），可在time_pairs中添加校验逻辑，或在最终查询中单独统计异常日期。

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