PostgreSQL跨天（夜间至次日早）数据提取与结构重组方案咨询

根据你的数据和期望输出，核心是将前一天19:00至当天08:00的连续数据归为"当天"的记录，并提取该时段的最早时间作为起始、最晚时间作为结束。这个思路完全匹配你给出的示例结果，是可行的。

实现SQL代码 #

WITH merged_data AS (
    -- 合并日期时间为完整时间戳，计算每条记录归属的目标日期
    SELECT
        (date || ' ' || time)::timestamp AS ts,
        -- 19点后的数据归为次日，0-8点数据归为当日
        CASE
            WHEN time >= '19:00:00' THEN date + INTERVAL '1 day'
            ELSE date
        END::date AS target_date
    FROM your_table_name
    -- 初步过滤出夜间时段数据
    WHERE time >= '19:00:00' OR time <= '08:00:00'
),
valid_groups AS (
    -- 精确校验数据是否属于目标日期对应的夜间时段
    SELECT
        target_date AS date,
        ts
    FROM merged_data
    WHERE ts >= (target_date - INTERVAL '1 day') + TIME '19:00:00'
      AND ts < target_date + TIME '08:00:00'
)
-- 分组提取时段内的最早/最晚时间
SELECT
    date,
    MIN(TO_CHAR(ts, 'HH24:MI:SS')) AS "Start Date",
    MAX(TO_CHAR(ts, 'HH24:MI:SS')) AS "End Date"
FROM valid_groups
GROUP BY date
ORDER BY date;

逻辑说明 #

1. merged_data：将分离的Date和Time字段合并为完整时间戳，同时判断每条数据归属的目标日期（比如2022-11-10 21:10的数据，归属到2022-11-11）。
2. valid_groups：进一步校验数据边界，确保每条记录确实落在目标日期对应的夜间时段（前一天19:00到当天08:00），避免边界错误。
3. 最终分组后提取时段内的最早、最晚时间，格式化为你需要的时间字符串。

如果业务需要调整夜间时段的边界（比如改成20:00至次日07:00），只需修改SQL中的时间阈值即可。

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