前言
写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; -- 数据库时区