Oracle 时间处理函数和操作符笔记_oracle获取当前时间戳

前言

写sql时经常用到时间处理函数，我整理了一份Oracle的常用sql笔记,供大家参考。
如果对你有帮助，请点赞支持~ 多谢🙏

笔记

-- 1. 获取当前日期和时间-- SYSDATE, SYSTIMESTAMP, CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMPSELECT 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, CASTSELECT 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, NUMTOYMINTERVALSELECT 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_BETWEENSELECT 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, ROUNDSELECT 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-01SELECT TO_CHAR(SYSDATE, \'YYYY\"年\"MM\"月\"DD\"日\"\') FROM DUAL; -- 2023年01月01日SELECT TO_CHAR(SYSDATE, \'HH24:MI:SS\') FROM DUAL; -- 14:30:45SELECT TO_CHAR(SYSDATE, \'Day, DD Month YYYY\') FROM DUAL; -- 星期几, 日 月 年-- 8. 生成时间序列-- 使用CONNECT BY或递归WITHSELECT TO_DATE(\'2023-01-01\', \'YYYY-MM-DD\') + LEVEL - 1FROM DUALCONNECT 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_TIMESELECT 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_DAYSELECT 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格式, EXTRACTSELECT TO_CHAR(SYSDATE, \'Q\') FROM DUAL;  -- 季度(1-4)SELECT EXTRACT(QUARTER FROM SYSDATE) FROM DUAL;  -- 季度-- 14. 间隔处理-- NUMTODSINTERVAL, NUMTOYMINTERVALSELECT NUMTODSINTERVAL(3, \'HOUR\') FROM DUAL;  -- 3小时间隔SELECT NUMTOYMINTERVAL(6, \'MONTH\') FROM DUAL; -- 6个月间隔-- 15. 高级时间函数-- ADD_MONTHS, NEXT_DAY, LAST_DAYSELECT 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_TZSELECT 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, DBTIMEZONESELECT SESSIONTIMEZONE FROM DUAL; -- 会话时区SELECT DBTIMEZONE FROM DUAL; -- 数据库时区

同款笔记

mysql时间处理函数和操作笔记