PostgreSQL中计算连续行VARCHAR类型时间差并验证是否为1小时

问题背景 #

PostgreSQL的myScheduleTable表存在如下数据，其中时间字段为VARCHAR类型：

ID  Time(varchar)  Time(varchar) Date
129 "08:30:00"     "15:45:00"    "2022-06-22"
139 "08:30:00"     "16:45:00"    "2022-06-22"
149 "08:30:00"     "17:45:00"    "2022-06-22"
159 "08:30:00"     "18:45:00"    "2022-06-22"
169 "08:30:00"     "19:45:00"    "2022-06-22"
179 "08:30:00"     "20:45:00"    "2022-06-22"
189 "08:30:00"     "21:30:00"    "2022-06-22"   // 无效案例
199 "08:30:00"     "22:45:00"    "2022-06-22"

需求是：检查按顺序排列的连续行中，后一行结束时间与前一行结束时间的间隔是否恰好为1小时，最终统计符合条件的连续行对数量（类似SELECT count(*) FROM myScheduleTable WHERE consecutiveBlocksTimeDifference = 1 hour;的效果）。

实现方案 #

完全可以在PostgreSQL中实现，核心是利用窗口函数关联连续行，结合时间类型转换计算差值，具体步骤如下：

1. 字符串转时间类型 #

由于时间字段是VARCHAR，需先转换为PostgreSQL可计算的时间类型：

• 若需结合日期计算完整时间戳，用TO_TIMESTAMP()拼接日期和时间；
• 若仅比较时间部分，用TIME()直接转换。

2. 用窗口函数获取前一行数据 #

使用LAG()窗口函数，按业务规则（示例中按ID排序）获取前一行的结束时间。

3. 计算时间差并筛选 #

计算当前行与前一行结束时间的差值，判断是否等于1小时，最后统计符合条件的记录数。

完整查询语句 #

场景1：结合日期计算完整时间戳

如果需要考虑日期变化（比如跨天的情况），使用该查询：

SELECT COUNT(*) AS valid_consecutive_pairs
FROM (
    SELECT 
        id,
        -- 拼接日期与结束时间，转换为时间戳
        TO_TIMESTAMP("Date" || ' ' || "Time(varchar)", 'YYYY-MM-DD HH24:MI:SS') AS current_end_ts,
        -- 获取前一行的结束时间戳
        LAG(TO_TIMESTAMP("Date" || ' ' || "Time(varchar)", 'YYYY-MM-DD HH24:MI:SS')) 
            OVER (ORDER BY id) AS prev_end_ts
    FROM myScheduleTable
) AS sub
WHERE 
    prev_end_ts IS NOT NULL  -- 排除无前置行的第一行
    -- 计算时间差为3600秒（即1小时）
    AND EXTRACT(EPOCH FROM (current_end_ts - prev_end_ts)) = 3600;

注：表中第二个Time(varchar)是结束时间，需根据实际字段名调整（建议给字段起有意义的名字，比如start_time和end_time）。

场景2：仅比较时间部分

如果无需考虑日期，只比较时间值的间隔：

SELECT COUNT(*) AS valid_consecutive_pairs
FROM (
    SELECT 
        id,
        TIME("Time(varchar)") AS current_end_time,
        -- 获取前一行的结束时间
        LAG(TIME("Time(varchar)")) OVER (ORDER BY id) AS prev_end_time
    FROM myScheduleTable
) AS sub
WHERE 
    prev_end_time IS NOT NULL
    -- 直接判断时间差为1小时
    AND current_end_time - prev_end_time = INTERVAL '1 hour';

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