SQL 跨夜時段查詢：每天 22:00 至隔天 09:00 的資料篩選技巧

需求

從 2015-01-01 到 2015-01-31，抽取每日 22:00 ~ 隔日 09:00 的記錄。資料表含 START_TIME、END_TIME 兩個 datetime 欄位。

錯誤嘗試

WHERE A.START_TIME BETWEEN '2015-01-01 22:00:00' AND '2015-01-31 09:00:00'
  AND A.END_TIME   BETWEEN '2015-01-01 22:00:00' AND '2015-01-31 09:00:00'

此寫法會把 1 月 2 日 14:00 的資料也撈進來，因為它落在整體區間內，但不符合「跨夜時段」定義。

正確邏輯：拆解日期與時間

1. 日期條件：START_TIME 日期在 1/1~1/31 之間。
2. 時間條件：START_TIME 時間部分 ∈ [22:00, 23:59:59] ∪ [00:00, 09:00]。
3. 同理套用至 END_TIME。
4. 可選：過濾跨度過長的髒資料（如 DATEDIFF(second, START_TIME, END_TIME) <= 39600 即 11 小時）。

通用寫法（MySQL / SQL Server / Oracle 通用思路）

WHERE DATE(START_TIME) BETWEEN '2015-01-01' AND '2015-01-31'
  AND (
        TIME(START_TIME) >= '22:00:00'
     OR TIME(START_TIME) <= '09:00:00'
      )
  AND DATE(END_TIME) BETWEEN '2015-01-01' AND '2015-01-31'
  AND (
        TIME(END_TIME) >= '22:00:00'
     OR TIME(END_TIME) <= '09:00:00'
      )
  AND TIMESTAMPDIFF(SECOND, START_TIME, END_TIME) <= 39600;

關鍵函數：DATE() 取日期部、TIME() 取時間部、TIMESTAMPDIFF 計算秒差。各資料庫函數名稱略有不同（如 SQL Server 用 CAST(... AS DATE)、DATEDIFF(SECOND, ...)），請依實際環境調整。

效能優化建議

• 在 START_TIME、END_TIME 建立複合索引。
• 避免在 WHERE 子句對欄位做函數運算（如 SUBSTRING），改用範圍查詢：
  START_TIME >= '2015-01-01 22:00:00' AND START_TIME < '2015-02-01 09:00:00' 搭配 OR 邏輯處理跨夜。
• 若資料量極大，考慮預先計算「所屬夜班日期」存入輔助欄位，查詢時直接等值比對。

掌握「日期拆分 + 時間區間聯集」模式，即可輕鬆解決所有跨夜班表統計需求。