萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

SQL 截取日期時間部分:DATEPART/YEAR/MONTH/DAY vs 日期運算錨點法

需按年/月/日統計,但資料存到秒。兩大流派:**日期部件函數** vs **日期運算錨點法**。後者可直接用於索引搜尋 (SARGable),效能優勝。

方法一:日期部件函數 (直觀但可能導致掃描)

-- 取年月日組合成日期 (去除時間)
CAST(
    STR(YEAR(GETDATE())) + '/' + 
    STR(MONTH(GETDATE())) + '/' + 
    STR(DAY(GETDATE()))
AS DATETIME);

-- 或直接用 DATEPART
SELECT DATEPART(YEAR, ColTime), DATEPART(MONTH, ColTime), DATEPART(DAY, ColTime);

缺點:WHERE YEAR(ColTime)=2024 導致索引失效,全表掃描。

方法二:日期運算錨點法 (SARGable、高效)

-- 取月首日 (00:00:00.000)
DATEADD(MONTH, DATEDIFF(MONTH, 0, ColTime), 0)

-- 取下月首日 (作為本月範圍上界)
DATEADD(MONTH, DATEDIFF(MONTH, 0, ColTime) + 1, 0)

-- 取日零點
DATEADD(DAY, DATEDIFF(DAY, 0, ColTime), 0)

-- 取年首日
DATEADD(YEAR, DATEDIFF(YEAR, 0, ColTime), 0)

原理圖解

ColTime (2024-03-15 14:30:25)
      │
      ▼ DATEDIFF(MONTH, 0, ColTime) = 1491 (從 1900-01 到 2024-03 的月數)
      │
      ▼ DATEADD(MONTH, 1491, 0) = 2024-03-01 00:00:00.000 (本月首日)
      │
      ▼ +1 月 → 2024-04-01 00:00:00.000 (下月首日,作為範圍上界)

查詢範例:本月資料 (索引友善)

DECLARE @StartOfMonth DATETIME = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);
DECLARE @StartOfNextMonth DATETIME = DATEADD(MONTH, 1, @StartOfMonth);

SELECT * FROM Orders
WHERE OrderTime >= @StartOfMonth
  AND OrderTime <  @StartOfNextMonth;  -- < 下月首日,精確包含本月所有毫秒

對比總結

維度DATEPART/YEAR/MONTH/DAYDATEDIFF+DATEADD 錨點法
可讀性高中
SARGable (索引可用)否 (函數包裹欄位)是 (欄位裸露)
適用場景SELECT 列表、GROUP BYWHERE 篩選、JOIN 條件、分區
精度控制需額外處理天然含時間精度

**最佳實踐**:SELECT/GROUP BY 用 DATEPART 可讀;WHERE/JOIN/分區條件**必須用錨點法**確保索引生效。

copyright © 萬盛學電腦網 all rights reserved