Oracle 日期時間函數實戰速查：格式化、運算、截取與常用查詢範本

基礎格式化與轉換

-- 日期 → 字串
SELECT TO_CHAR(SYSDATE, 'YYYY.MM.DD HH24:MI:SS') FROM DUAL;

-- 字串 → 日期
SELECT TO_DATE('2008-01-02', 'YYYY-MM-DD') FROM DUAL;

-- 錯誤示範：TO_DATE 不接受 DATE 型別輸入
-- SELECT TO_DATE(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL; -- ORA-01843

日期運算

-- 日期相減 = 天數差 (含小數)
SELECT TO_DATE('2008-01-02','YYYY-MM-DD') - TO_DATE('2008-01-01','YYYY-MM-DD') FROM DUAL; -- 1

-- 範圍查詢最佳寫法：半開區間 [start, end+1)
SELECT * FROM aa
WHERE t >= TO_DATE('2008-01-01','YYYY-MM-DD')
  AND t <  TO_DATE('2008-10-21','YYYY-MM-DD');

EXTRACT 截取元件

SELECT EXTRACT(HOUR   FROM TIMESTAMP '2001-02-16 02:38:40') AS hr,
       EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 02:38:40') AS mi,
       EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 02:38:40') AS ss,
       EXTRACT(DAY    FROM TIMESTAMP '2001-02-16 02:38:40') AS dd,
       EXTRACT(MONTH  FROM TIMESTAMP '2001-02-16 02:38:40') AS mm,
       EXTRACT(YEAR   FROM TIMESTAMP '2001-02-16 02:38:40') AS yyyy
FROM DUAL;

常用業務查詢範本

今日資料 (00:00:00 ~ 23:59:59)

SELECT * FROM tdb_data_jcyssj
WHERE uploadtime >= TRUNC(SYSDATE)
  AND uploadtime <  TRUNC(SYSDATE) + 1;

取得「今天 00:00:00」與「明天 00:00:00」

SELECT TRUNC(SYSDATE)         AS today_0000,
       TRUNC(SYSDATE) + 1     AS tomorrow_0000
FROM DUAL;

取得「今天 23:59:59」

SELECT TRUNC(SYSDATE) + 1 - 1/86400 AS today_235959 FROM DUAL;

判斷週幾 (週日=1)

SELECT TO_CHAR(SYSDATE, 'D') AS day_of_week,
       TO_CHAR(SYSDATE, 'DAY') AS day_name
FROM DUAL;

本月第一天 / 最後一天

SELECT TO_CHAR(SYSDATE, 'YYYY-MM') || '-01' AS first_day,
       TO_CHAR(LAST_DAY(SYSDATE), 'YYYY-MM-DD') AS last_day
FROM DUAL;

上月第一天 / 本月第一天 / 本月最後一天 (含時間)

SELECT TO_CHAR(TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE), -2) + 1), 'YYYY-MM-DD HH24:MI:SS') AS last_month_first,
       TO_CHAR(TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE), -1) + 1), 'YYYY-MM-DD HH24:MI:SS') AS this_month_first,
       TO_CHAR(LAST_DAY(SYSDATE), 'YYYY-MM-DD HH24:MI:SS') AS this_month_last
FROM DUAL;

動態基準時間：16:00 前取「昨天 16:00」，16:00 後取「今天 16:00」

SELECT field1, field2
FROM mytable
WHERE time = DECODE(SIGN(SYSDATE - TRUNC(SYSDATE) - 16/24),
                      -1, TRUNC(SYSDATE) - 1 + 16/24,  -- 昨天 16:00
                          TRUNC(SYSDATE) + 16/24);      -- 今天 16:00

效能提醒

• 欄位上加函數 (如 WHERE TRUNC(t) = ...) 會導致索引失效，改用半開區間 t >= ... AND t < ...。
• SYSDATE 回傳資料庫伺服器時間；若需 Session 時區時間，改用 CURRENT_DATE / SYSTIMESTAMP。