前言

寫sql時經常用到時間處理函數，我整理了一份Oracle的常用sql筆記,供大家參考。
如果對你有幫助，請點贊支持~ 多謝🙏

筆記

-- 1. 獲取當前日期和時間
-- SYSDATE, SYSTIMESTAMP, CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP
SELECT SYSDATE FROM DUAL;                     -- 當前日期和時間(數據庫服務器時間)
SELECT SYSTIMESTAMP FROM DUAL;                -- 帶時區的時間戳
SELECT CURRENT_DATE FROM DUAL;                -- 會話當前日期
SELECT CURRENT_TIMESTAMP FROM DUAL;           -- 帶時區的時間戳(會話時間)
SELECT LOCALTIMESTAMP FROM DUAL;              -- 不帶時區的時間戳(會話時間)-- 2. 時間類型轉換
-- TO_CHAR, TO_DATE, TO_TIMESTAMP, TO_TIMESTAMP_TZ, CAST
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL;          -- 日期轉字符串
SELECT TO_DATE('2023-01-01', 'YYYY-MM-DD') FROM DUAL;     -- 字符串轉日期
SELECT TO_TIMESTAMP('2023-01-01 14:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_TIMESTAMP_TZ('2023-01-01 14:30:00 +08:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM') FROM DUAL;
SELECT CAST(SYSDATE AS TIMESTAMP) FROM DUAL;              -- 類型轉換-- 3. 提取時間部分
-- EXTRACT, TO_NUMBER組合
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;              -- 提取年份
SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL;             -- 提取月份
SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL;               -- 提取日
SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) FROM DUAL;     -- 提取小時
SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'MI')) FROM DUAL;       -- 提取分鐘-- 4. 時間運算
-- 直接加減(天數), NUMTODSINTERVAL, NUMTOYMINTERVAL
SELECT SYSDATE + 1 FROM DUAL;                             -- 加1天
SELECT SYSDATE - 7 FROM DUAL;                             -- 減7天
SELECT SYSDATE + NUMTODSINTERVAL(3, 'HOUR') FROM DUAL;    -- 加3小時
SELECT SYSDATE + NUMTOYMINTERVAL(2, 'MONTH') FROM DUAL;   -- 加2個月-- 5. 時間差計算
-- 直接相減(天數), MONTHS_BETWEEN
SELECT SYSDATE - TO_DATE('2023-01-01', 'YYYY-MM-DD') FROM DUAL; -- 天數差
SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2023-01-01', 'YYYY-MM-DD')) FROM DUAL; -- 月數差-- 6. 時間截斷
-- TRUNC, ROUND
SELECT TRUNC(SYSDATE, 'YEAR') FROM DUAL;                  -- 截斷到年初
SELECT TRUNC(SYSDATE, 'MONTH') FROM DUAL;                 -- 截斷到月初
SELECT TRUNC(SYSDATE, 'DAY') FROM DUAL;                   -- 截斷到周初(周日)
SELECT ROUND(SYSDATE, 'MONTH') FROM DUAL;                 -- 四舍五入到月-- 7. 時間格式化
-- TO_CHAR模式
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL;          -- 2023-01-01
SELECT TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日"') FROM DUAL; -- 2023年01月01日
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM DUAL;          -- 14:30:45
SELECT TO_CHAR(SYSDATE, 'Day, DD Month YYYY') FROM DUAL;   -- 星期幾, 日 月 年-- 8. 生成時間序列
-- 使用CONNECT BY或遞歸WITH
SELECT TO_DATE('2023-01-01', 'YYYY-MM-DD') + LEVEL - 1
FROM DUAL
CONNECT BY LEVEL <= 10;                                   -- 生成10天序列-- 9. 時間比較
-- 常規比較操作符(>, <, =, >=, <=)
SELECT * FROM DUAL WHERE SYSDATE > TO_DATE('2023-01-01', 'YYYY-MM-DD');-- 10. 特殊時間值
-- Oracle沒有無限時間概念，但可以使用極值
SELECT TO_DATE('4712-01-01', 'YYYY-MM-DD') FROM DUAL;      -- 最小日期
SELECT TO_DATE('9999-12-31', 'YYYY-MM-DD') FROM DUAL;     -- 最大日期-- 11. 時區處理
-- FROM_TZ, AT TIME ZONE, TZ_OFFSET, NEW_TIME
SELECT FROM_TZ(TIMESTAMP '2023-01-01 12:00:00', 'Asia/Shanghai') AT TIME ZONE 'UTC' FROM DUAL;
SELECT TZ_OFFSET('Asia/Shanghai') FROM DUAL;              -- 時區偏移量
SELECT NEW_TIME(SYSDATE, 'PST', 'EST') FROM DUAL;         -- 舊時區轉換函數-- 12. 星期相關函數
-- TO_CHAR的DAY/DY格式, NEXT_DAY, LAST_DAY
SELECT TO_CHAR(SYSDATE, 'DAY') FROM DUAL;                 -- 星期幾全稱
SELECT TO_CHAR(SYSDATE, 'DY') FROM DUAL;                  -- 星期幾縮寫
SELECT NEXT_DAY(SYSDATE, 'MONDAY') FROM DUAL;             -- 下一個星期一
SELECT LAST_DAY(SYSDATE) FROM DUAL;                       -- 當月最后一天-- 13. 季度計算
-- TO_CHAR的Q格式, EXTRACT
SELECT TO_CHAR(SYSDATE, 'Q') FROM DUAL;                   -- 季度(1-4)
SELECT EXTRACT(QUARTER FROM SYSDATE) FROM DUAL;           -- 季度-- 14. 間隔處理
-- NUMTODSINTERVAL, NUMTOYMINTERVAL
SELECT NUMTODSINTERVAL(3, 'HOUR') FROM DUAL;              -- 3小時間隔
SELECT NUMTOYMINTERVAL(6, 'MONTH') FROM DUAL;             -- 6個月間隔-- 15. 高級時間函數
-- ADD_MONTHS, NEXT_DAY, LAST_DAY
SELECT ADD_MONTHS(SYSDATE, 3) FROM DUAL;                  -- 加3個月
SELECT NEXT_DAY(SYSDATE, 'FRIDAY') FROM DUAL;             -- 下一個星期五
SELECT LAST_DAY(SYSDATE) FROM DUAL;                       -- 當月最后一天-- 16. 時間戳函數
-- SYSTIMESTAMP, TO_TIMESTAMP, TO_TIMESTAMP_TZ
SELECT SYSTIMESTAMP FROM DUAL;                            -- 帶時區的時間戳
SELECT TO_TIMESTAMP('2023-01-01 14:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_TIMESTAMP_TZ('2023-01-01 14:30:00 +08:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM') FROM DUAL;-- 17. 會話時間函數
-- SESSIONTIMEZONE, DBTIMEZONE
SELECT SESSIONTIMEZONE FROM DUAL;                         -- 會話時區
SELECT DBTIMEZONE FROM DUAL;                              -- 數據庫時區

同款筆記

mysql時間處理函數和操作筆記