WITH SYS_TIME AS ( SELECT TRUNC(TO_DATE('2025-07-02 22:33:13', 'yyyy-mm-dd hh24:mi:ss'), 'DD') + (INTERVAL_NUM * 10) / (24 * 60) AS DATETIME FROM (SELECT LEVEL - 1 AS INTERVAL_NUM FROM DUAL CONNECT BY LEVEL <= 144) ORDER BY INTERVAL_NUM ), TIME_BASE AS ( SELECT DATETIME, TO_CHAR(DATETIME, 'HH24:MI') MINUTE_VAL, CAST(TO_CHAR(DATETIME, 'HH24') AS NUMBER) HOUR_VAL, CASE WHEN TO_CHAR(DATETIME, 'HH24:MI:SS') >= '08:00:00' AND TO_CHAR(DATETIME, 'HH24:MI:SS') < '20:00:00' THEN '白班' ELSE '夜班' END AS CLASS_TYPE, CASE WHEN CAST(TO_CHAR(DATETIME, 'HH24') AS NUMBER) >= 20 THEN 0 WHEN CAST(TO_CHAR(DATETIME, 'HH24') AS NUMBER) < 8 THEN 1 ELSE 2 END SORT_NO FROM SYS_TIME ) SELECT MINUTE_VAL,DATETIME FROM ( SELECT CASE WHEN SORT_NO = 0 AND TO_CHAR(TO_DATE('2025-07-02 22:33:13', 'yyyy-mm-dd hh24:mi:ss'), 'hh24:mi:ss') < '08:00:00' THEN DATETIME-1 WHEN SORT_NO = 1 AND TO_CHAR(TO_DATE('2025-07-02 22:33:13', 'yyyy-mm-dd hh24:mi:ss'), 'hh24:mi:ss') > '20:00:00' THEN DATETIME + 1 ELSE DATETIME END DATETIME, MINUTE_VAL, HOUR_VAL, CLASS_TYPE, SORT_NO FROM ( SELECT DATETIME, MINUTE_VAL, HOUR_VAL, CASE WHEN MINUTE_VAL = '08:00' THEN '夜班' WHEN MINUTE_VAL = '20:00' THEN '白班' ELSE CLASS_TYPE END CLASS_TYPE, CASE WHEN MINUTE_VAL = '08:00' THEN 1 WHEN MINUTE_VAL = '20:00' THEN 0 ELSE SORT_NO END SORT_NO FROM TIME_BASE ) ) WHERE CLASS_TYPE = '夜班' AND DATETIME <= TO_DATE('2025-07-02 22:33:13','yyyy-mm-dd hh24:mi:ss') + 10/60/24 ORDER BY SORT_NO, HOUR_VAL, MINUTE_VAL
白夜班时间分割SQL
点赞
收藏