Oracle 的日期時間處理與其他資料庫有不少差異，整理常用的時間資料型別、運算函式與格式轉換方式，方便日後查閱。
時間資料型別
| 型別 |
說明 |
DATE |
儲存日期與時間，精確到秒（年月日時分秒），不含時區資訊 |
TIMESTAMP |
比 DATE 更精確，可到小數秒（最高 9 位小數），不含時區 |
TIMESTAMP WITH TIME ZONE |
含時區資訊的時間戳記 |
TIMESTAMP WITH LOCAL TIME ZONE |
轉換為資料庫時區後儲存 |
INTERVAL YEAR TO MONTH |
表示年月間隔 |
INTERVAL DAY TO SECOND |
表示天到秒的間隔 |
取得當前時間
1
2
3
4
5
6
7
8
|
-- 取得當前日期與時間（DATE 型別）
SELECT SYSDATE FROM DUAL;
-- 取得當前時間戳記（TIMESTAMP 型別）
SELECT SYSTIMESTAMP FROM DUAL;
-- 取得當前日期（只有日期部分）
SELECT TRUNC(SYSDATE) FROM DUAL;
|
日期加減運算
Oracle 的 DATE 型別可以直接加減數字，單位為天：
1
2
3
4
5
6
7
8
9
10
11
|
-- 加 7 天
SELECT SYSDATE + 7 FROM DUAL;
-- 減 1 天
SELECT SYSDATE - 1 FROM DUAL;
-- 加 3 小時（1/24 天）
SELECT SYSDATE + 3/24 FROM DUAL;
-- 加 30 分鐘（30/(24*60) 天）
SELECT SYSDATE + 30/1440 FROM DUAL;
|
ADD_MONTHS：加減月份
直接加減月份不能用數字，必須用 ADD_MONTHS 函式：
1
2
3
4
5
|
-- 加 3 個月
SELECT ADD_MONTHS(SYSDATE, 3) FROM DUAL;
-- 減 6 個月
SELECT ADD_MONTHS(SYSDATE, -6) FROM DUAL;
|
MONTHS_BETWEEN：計算月份差
1
2
3
|
-- 計算兩個日期相差幾個月
SELECT MONTHS_BETWEEN(DATE '2024-06-01', DATE '2024-01-01') FROM DUAL;
-- 結果：5
|
TRUNC 與 ROUND：截斷與四捨五入
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
-- 截斷到當天 00:00:00
SELECT TRUNC(SYSDATE) FROM DUAL;
-- 截斷到本月第一天
SELECT TRUNC(SYSDATE, 'MM') FROM DUAL;
-- 截斷到本年第一天
SELECT TRUNC(SYSDATE, 'YYYY') FROM DUAL;
-- 四捨五入到最近的日期
SELECT ROUND(SYSDATE, 'DD') FROM DUAL;
-- 四捨五入到最近的月份（15 號之後進到下個月）
SELECT ROUND(SYSDATE, 'MM') FROM DUAL;
|
計算兩個日期的時間差
1
2
3
4
5
6
7
8
|
-- 相差幾天（SYSDATE - 某日期，回傳天數）
SELECT SYSDATE - DATE '2024-01-01' FROM DUAL;
-- 相差幾小時
SELECT (SYSDATE - DATE '2024-01-01') * 24 FROM DUAL;
-- 相差幾分鐘
SELECT (SYSDATE - DATE '2024-01-01') * 24 * 60 FROM DUAL;
|
查詢昨天的資料
1
2
3
4
5
6
|
-- 查詢昨天整天的資料
SELECT *
FROM orders oh
WHERE oh.tran_date BETWEEN TRUNC(SYSDATE - 1) AND TRUNC(SYSDATE) - 1/86400;
-- TRUNC(SYSDATE - 1) = 昨天 00:00:00
-- TRUNC(SYSDATE) - 1/86400 = 昨天 23:59:59
|
格式轉換
TO_DATE：字串轉日期
1
2
3
|
-- 字串轉 DATE
SELECT TO_DATE('2024-06-15', 'YYYY-MM-DD') FROM DUAL;
SELECT TO_DATE('2024/06/15 14:30:00', 'YYYY/MM/DD HH24:MI:SS') FROM DUAL;
|
TO_CHAR：日期轉字串
1
2
3
4
5
6
7
8
9
|
-- 日期轉字串
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') FROM DUAL;
-- 取得星期幾
SELECT TO_CHAR(SYSDATE, 'DAY') FROM DUAL; -- 例：WEDNESDAY
-- 格式化為中文日期
SELECT TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日"') FROM DUAL;
|
常用格式代碼：
| 格式 |
說明 |
YYYY |
四位年份 |
MM |
月份（01-12） |
DD |
日期（01-31） |
HH24 |
24 小時制的小時 |
MI |
分鐘 |
SS |
秒 |
DAY |
星期幾全稱 |
DY |
星期幾縮寫 |
參考資料