PostgreSQL如何选取与下一时间间隔≥3小时的当日首个时间

解决方案 #

你可以使用PostgreSQL内置的窗口函数LEAD()实现需求，具体逻辑和SQL语句如下：

实现逻辑 #

• 用LEAD()窗口函数按时间字段升序排序后，读取每条记录对应的下一条记录的时间值
• 计算当前记录与下一条记录的时间间隔，筛选出间隔≥3小时的记录
• 对筛选结果按时间升序排序后取第一条，即为当日第一个满足要求的时间值

示例SQL #

将SQL中的your_table替换为你的实际表名即可运行：

SELECT dates
FROM (
    SELECT
        dates,
        -- 按时间升序取当前行的下一行时间
        LEAD(dates) OVER (ORDER BY dates ASC) AS next_dates
    FROM your_table
    -- 如果需要限定查询日期可放开下方注释，替换为对应日期即可
    -- WHERE dates::DATE = '2021-06-24'
) AS temp
WHERE next_dates - dates >= INTERVAL '3 hours'
ORDER BY dates ASC
LIMIT 1;

多日期场景适配 #

如果你的表中存在多天的数据，需要每日返回一个符合要求的时间值，可以修改窗口函数部分，按日期分区计算：

LEAD(dates) OVER (PARTITION BY dates::DATE ORDER BY dates ASC) AS next_dates

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