在Oracle中使用SQL创建列出两个时间戳间小时的列

以下是直接可用的SQL语句，基于递归CTE生成start和finish字段之间包含的所有小时数，并用逗号拼接成hour_list列：

WITH hour_range AS (
    SELECT
        start AS start_ts,
        finish AS finish_ts,
        -- 计算起始小时（跨天则累加天数*24）
        EXTRACT(HOUR FROM start) + 24*(TRUNC(start) - TRUNC(start)) AS start_hour,
        -- 计算结束小时（跨天则累加天数*24）
        EXTRACT(HOUR FROM finish) + 24*(TRUNC(finish) - TRUNC(start)) AS end_hour
    FROM Table1
),
hour_cte AS (
    SELECT
        start_ts,
        finish_ts,
        start_hour AS current_hour
    FROM hour_range
    UNION ALL
    SELECT
        start_ts,
        finish_ts,
        current_hour + 1
    FROM hour_cte
    JOIN hour_range 
        ON hour_cte.start_ts = hour_range.start_ts 
        AND hour_cte.finish_ts = hour_range.finish_ts
    WHERE current_hour < hour_range.end_hour + 1
)
SELECT
    start_ts AS start,
    finish_ts AS finish,
    -- 取小时的0-23格式，按顺序拼接
    LISTAGG(MOD(current_hour, 24), ',') WITHIN GROUP (ORDER BY current_hour) AS hour_list
FROM hour_cte
-- 过滤出与时间区间有交集的小时
WHERE TRUNC(start_ts) + NUMTODSINTERVAL(current_hour, 'HOUR') < finish_ts + INTERVAL '1' HOUR
  AND TRUNC(start_ts) + NUMTODSINTERVAL(current_hour + 1, 'HOUR') > start_ts
GROUP BY start_ts, finish_ts;

逻辑说明 #

1. hour_range CTE：先计算每条记录的起始小时和结束小时，跨天场景下会把天数转化为小时数累加（比如次日2点会计算为2+24=26），确保区间覆盖完整。
2. hour_cte递归CTE：从起始小时开始，逐个生成后续小时数，直到超过结束小时+1（保证包含结束时间所在的小时）。
3. LISTAGG拼接：将生成的小时数转换为0-23的格式，按时间顺序拼接成逗号分隔的字符串。
4. 过滤条件：确保生成的小时时间段与start到finish的区间有重叠，避免无意义的小时被包含。

示例验证 #

针对你给出的Table1数据，执行上述SQL后会返回与示例完全一致的结果：

start                     finish                      hour_list

1/7/2024 4:57:00 AM 1/7/2024 1:29:00 PM 4,5,6,7,8,9,10,11,12,13
1/7/2024 1:29:00 PM 1/7/2024 3:10:00 PM 13,14,15
1/8/2024 4:40:00 AM 1/8/2024 11:10:00 AM 4,5,6,7,8,9,10,11

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