核心函數分類速查
| 類別 | 函數 | 說明 |
|---|
| 日期屬性提取 | YEAR/MONTH/DAY | 從日期/時間戳提取年/月/日 |
DAYOFWEEK | 1=周日 ... 7=周六 |
DAYOFWEEK_ISO | 1=周一 ... 7=周日 (ISO 標準) |
| 時間屬性提取 | HOUR/MINUTE/SECOND/MICROSECOND | 從時間/時間戳提取時/分/秒/微秒 |
MIDNIGHT_SECONDS | 午夜至指定時間秒數 (0-86400) |
| 字符串表示 | DAYNAME | 星期名稱 (如 Friday) |
MONTHNAME | 月份名稱 (如 January) |
CHAR(date/time/timestamp) | 轉預設格式字符串，便於拼接 |
| 格式化/解析 | TO_CHAR / VARCHAR_FORMAT | 時間戳按模板格式化為字符串 |
TO_DATE / TIMESTAMP_FORMAT | 字符串按模板解析為時間戳 |
TIMESTAMP_ISO | 日期/時間/時間戳 → 標準時間戳 |
TIMESTAMP | 多種字符串格式 → 時間戳 |
| 日期運算 | date + 1 YEAR / 3 MONTHS / 15 DAYS | 英文自然語言加減運算 |
DAYS(date) | 日期轉整數 (從 0001-01-01 起)，支援減法求天差 |
| 精確時間差 | TIMESTAMPDIFF(n, char(ts1-ts2)) | 近似差值，n=1微秒..256年，假設每月 30 天、無閏年 |
(DAYS(ts1)-DAYS(ts2))*86400 + (MIDNIGHT_SECONDS(ts1)-MIDNIGHT_SECONDS(ts2)) | 精確秒差，建議封裝為 UDF secondsdiff |
| 其他 | JULIAN_DAY | 儒略日 (公元前 4712-01-01 起算) |
WEEK / WEEK_ISO | 年內週數 (1-54 / 1-53) |
實戰常用模式
1. 當前時間轉 UTC
CURRENT TIME - CURRENT TIMEZONE
CURRENT TIMESTAMP - CURRENT TIMEZONE
2. 日期邊界計算
-- 本月第一天
CURRENT DATE - (DAY(CURRENT DATE)-1) DAYS
-- 本月最後一天
(CURRENT DATE - (DAY(CURRENT DATE)-1) DAYS) + 1 MONTH - 1 DAY
3. 精確時間差 UDF
CREATE FUNCTION secondsdiff(t1 TIMESTAMP, t2 TIMESTAMP)
RETURNS INT
RETURN (DAYS(t1)-DAYS(t2))*86400 + (MIDNIGHT_SECONDS(t1)-MIDNIGHT_SECONDS(t2))@
4. 閏年判斷 UDF
CREATE FUNCTION daysinyear(yr INT)
RETURNS INT
RETURN CASE MOD(yr,400) WHEN 0 THEN 366
ELSE CASE MOD(yr,4) WHEN 0 THEN CASE MOD(yr,100) WHEN 0 THEN 365 ELSE 366 END
ELSE 365 END END@
5. 字符串拼接報表
SELECT 'Report Date: ' || CHAR(CURRENT DATE) FROM SYSIBM.SYSDUMMY1
最佳實踐提醒
- 優先使用 精確運算 (DAYS + MIDNIGHT_SECONDS) 而非
TIMESTAMPDIFF 進行業務級時間差計算
- 格式化輸出用
TO_CHAR 明確模板，避免隱式轉換隨地區設定變化
- 解析輸入嚴格指定模板
TO_DATE('2024-01-15','YYYY-MM-DD')
- 將常用邏輯封裝為 UDF，統一口徑、提升可維護性
- 注意
DAYOFWEEK (美制) 與 DAYOFWEEK_ISO (國際制) 差異，業務邏輯選對一個
