跟我學 MS SQL Server（九）：日期時間與間隔資料類型完整指南

一、SQL 標準日期時間類型體系

SQL:1992 (SQL-92) 定義了三大核心日期時間類型：`DATE`、`TIME`、`TIMESTAMP`，並提供 `WITH TIME ZONE` 變體。各資料庫廠商實作長度、格式、精度略有差異，但核心語義一致。本文依標準規格說明，實際開發請參閱對應資料庫文件。

二、DATE：純日期型別

SQL Server 對應：`DATE` (2008+)，2005 僅有 `DATETIME`/`SMALLDATETIME`，需自行剖析日期部分。

三、TIME：純時間型別

TIME WITH TIME ZONE：`TIME(precision) WITH TIME ZONE`，含時區位移 `+hh:mm` (範圍 -12:59 ~ +13:00)，總長 14 + precision + 1 (分隔符)。

SQL Server 對應：`TIME` (2008+)，精度 0~7 (100ns)；2005 無獨立 TIME 型別。

四、TIMESTAMP：日期時間合一

TIMESTAMP WITH TIME ZONE：含時區位移，總長 25 + precision + 1。

SQL Server 對應：

• `DATETIME`：1753-01-01 ~ 9999-12-31，精度 3.33ms (1/300 秒)，8 位元組
• `SMALLDATETIME`：1900-01-01 ~ 2079-06-06，精度 1 分鐘，4 位元組
• `DATETIME2` (2008+)：精度 0~7 (100ns)，6-8 位元組
• `DATETIMEOFFSET` (2008+)：含時區位移

五、INTERVAL：時間間隔型別

SQL-92 僅定義單一 `INTERVAL` 類型，兩種限定詞：

• 可正可負，前導精度指定最大位數
• 比較規則：同類型間比較，結果保持最大精度，必要時補零
• 僅秒欄位可含小數，其餘為整數

SQL Server 對應：無原生 INTERVAL 型別。常用做法：

• 儲存為 `BIGINT` (毫秒/微秒/奈秒) 或 `INT` (天數)
• 計算使用 `DATEDIFF` / `DATEADD` 函數
• 2016+ 可用 `DATEDIFF_BIG` 避免溢位

六、日期時間運算與函數

標準運算子

• `datetime + interval → datetime`
• `datetime - datetime → interval`
• `interval + interval → interval`
• `interval * numeric → interval`

關鍵字 (廠商實作差異大)

• `OVERLAPS`：判斷兩個期間是否重疊 `(start1, end1) OVERLAPS (start2, end2)`
• `EXTRACT(field FROM source)`：擷取欄位 `YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE`

SQL Server 等效函數

七、時區處理最佳實踐

1. 儲存統一用 UTC：應用層/中介層統一轉換，資料庫僅存 UTC
2. 顯示時轉換：前端依用戶時區轉換，避免資料庫層處理時區邏輯
3. 2008+ 使用 `DATETIMEOFFSET`：原生保存時區位移，查詢可直接 `AT TIME ZONE` (2016+)
4. 避免 `GETDATE()` 做業務邏輯：改用 `GETUTCDATE()`，確保跨時區一致性

八、常見陷阱

1. 精度不匹配截斷：`DATETIME` 精度 3.33ms，`DATETIME2(7)` 100ns，跨型別比較需 `CAST`
2. 字串隱式轉換失敗：語言/日期格式設定影響 `CAST('2023-01-02' AS DATETIME)`，建議用 ISO 8601 `YYYYMMDD` 或 `YYYY-MM-DDTHH:MM:SS`
3. 閏秒/夏令時間：UTC 無閏秒問題，本地時間轉換需用 `TZ` 資料庫 (如 NodaTime)
4. INTERVAL 缺失：SQL Server 用 `DATEDIFF` 回傳 `INT`，大間隔易溢位，2016+ 用 `DATEDIFF_BIG`

下一篇將進入數值資料類型篇，詳細解析 `NUMERIC`、`DECIMAL`、`FLOAT`、`REAL`、`MONEY` 等型別精度、刻度、儲存大小與運算規則。