Oracle SQL 日期与时间处理

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

Oracle 日期时间处理[1]：

详细见：Oracle SQL 日期与时间处理。

---

2. 类型

2.1 DATE

CREATE TABLE t (hire_date DATE);

• 日期 + 时间（秒）
• 公元前 4712 - 公元 9999

2.2 TIMESTAMP

CREATE TABLE t (
  ts1 TIMESTAMP,
  ts2 TIMESTAMP(6),
  ts3 TIMESTAMP(9)
);

• 纳秒精度

2.3 TIMESTAMP WITH TIME ZONE

CREATE TABLE t (ts TIMESTAMP WITH TIME ZONE);

2.4 TIMESTAMP WITH LOCAL TIME ZONE

CREATE TABLE t (ts TIMESTAMP WITH LOCAL TIME ZONE);
-- 数据库时区存储，会话显示本地

2.5 INTERVAL

CREATE TABLE t (
  duration INTERVAL DAY(2) TO SECOND(3),
  age INTERVAL YEAR(3) TO MONTH
);

详细见：Oracle 数据类型详解。

---

3. 函数

3.1 系统

SELECT SYSDATE, SYSTIMESTAMP, CURRENT_DATE, CURRENT_TIMESTAMP FROM dual;
SELECT LOCALTIMESTAMP, SESSIONTIMEZONE, DBTIMEZONE FROM dual;

3.2 计算

-- 加减
SELECT SYSDATE + 1 FROM dual;  -- 明天
SELECT SYSDATE - 1 FROM dual;  -- 昨天
SELECT SYSDATE + 1/24 FROM dual;  -- 一小时后
SELECT SYSDATE + 1/24/60 FROM dual;  -- 一分钟后

-- INTERVAL
SELECT SYSDATE + INTERVAL '1' DAY FROM dual;
SELECT SYSDATE + INTERVAL '1' HOUR FROM dual;
SELECT SYSDATE + INTERVAL '1-2' YEAR TO MONTH FROM dual;

-- 函数
SELECT ADD_MONTHS(SYSDATE, 3) FROM dual;
SELECT MONTHS_BETWEEN(DATE '2026-12-01', DATE '2026-01-01') FROM dual;
SELECT LAST_DAY(SYSDATE) FROM dual;
SELECT NEXT_DAY(SYSDATE, 'MONDAY') FROM dual;

3.3 提取

SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;
SELECT EXTRACT(MONTH FROM SYSDATE) FROM dual;
SELECT EXTRACT(DAY FROM SYSDATE) FROM dual;
SELECT EXTRACT(HOUR FROM SYSTIMESTAMP) FROM dual;
SELECT EXTRACT(MINUTE FROM SYSTIMESTAMP) FROM dual;
SELECT EXTRACT(SECOND FROM SYSTIMESTAMP) FROM dual;

3.4 截断/四舍五入

SELECT TRUNC(SYSDATE) FROM dual;  -- 0 点
SELECT TRUNC(SYSDATE, 'YYYY') FROM dual;  -- 年初
SELECT TRUNC(SYSDATE, 'MM') FROM dual;  -- 月初
SELECT TRUNC(SYSDATE, 'DAY') FROM dual;  -- 周初
SELECT TRUNC(SYSDATE, 'HH24') FROM dual;  -- 小时
SELECT TRUNC(SYSDATE, 'MI') FROM dual;  -- 分钟

SELECT ROUND(SYSDATE) FROM dual;  -- 中午前后
SELECT ROUND(SYSDATE, 'YYYY') FROM dual;
SELECT ROUND(SYSDATE, 'MM') FROM dual;

---

4. 格式化

4.1 TO_CHAR

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, 'YYYY"年"MM"月"DD"日"') FROM dual;
SELECT TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') FROM dual;
SELECT TO_CHAR(SYSDATE, 'D') FROM dual;  -- 周几（1-7）
SELECT TO_CHAR(SYSDATE, 'DAY') FROM dual;  -- 周几名
SELECT TO_CHAR(SYSDATE, 'IW') FROM dual;  -- ISO 周
SELECT TO_CHAR(SYSDATE, 'Q') FROM dual;  -- 季度
SELECT TO_CHAR(SYSDATE, 'J') FROM dual;  -- 儒略日

4.2 格式

代码	说明
YYYY	4 位年
YY	2 位年
MM	月
DD	日
HH24	24 小时
HH12	12 小时
MI	分
SS	秒
FF	小数秒
DY	周几缩写
DAY	周几全
MON	月缩写
MONTH	月全
AM/PM	上午/下午
Q	季度
WW	周
IW	ISO 周

4.3 TO_DATE

SELECT TO_DATE('2026-07-21', 'YYYY-MM-DD') FROM dual;
SELECT TO_DATE('2026/07/21 14:30:00', 'YYYY/MM/DD HH24:MI:SS') FROM dual;
SELECT TO_TIMESTAMP('2026-07-21 14:30:00.123', 'YYYY-MM-DD HH24:MI:SS.FF') FROM dual;

---

5. 时区

5.1 数据库

SELECT DBTIMEZONE FROM dual;
ALTER DATABASE SET TIME_ZONE = '+08:00';

5.2 会话

SELECT SESSIONTIMEZONE FROM dual;
ALTER SESSION SET TIME_ZONE = '+08:00';
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';

5.3 转换

SELECT FROM_TZ(TIMESTAMP '2026-07-21 14:00:00', '+00:00') FROM dual;
SELECT AT TIME ZONE
  FROM_TZ(TIMESTAMP '2026-07-21 14:00:00', '+00:00') AT TIME ZONE 'Asia/Shanghai';

---

6. INTERVAL

6.1 字面量

-- DAY TO SECOND
INTERVAL '5 12:30:00' DAY TO SECOND  -- 5 天 12:30:00
INTERVAL '1' DAY
INTERVAL '12' HOUR
INTERVAL '30' MINUTE
INTERVAL '60' SECOND

-- YEAR TO MONTH
INTERVAL '30-6' YEAR TO MONTH  -- 30 年 6 月
INTERVAL '1' YEAR
INTERVAL '6' MONTH

6.2 函数

SELECT NUMTODSINTERVAL(5, 'DAY') FROM dual;
SELECT NUMTOYMINTERVAL(30, 'YEAR') FROM dual;

-- 计算
SELECT SYSDATE + NUMTOYMINTERVAL(1, 'MONTH') FROM dual;
SELECT SYSDATE + NUMTODSINTERVAL(2, 'HOUR') FROM dual;

---

7. 应用场景

7.1 查询

-- 今天
SELECT * FROM t WHERE date_col = TRUNC(SYSDATE);

-- 本周
SELECT * FROM t 
WHERE date_col >= TRUNC(SYSDATE, 'IW') 
  AND date_col < TRUNC(SYSDATE, 'IW') + 7;

-- 本月
SELECT * FROM t 
WHERE date_col >= TRUNC(SYSDATE, 'MM') 
  AND date_col < ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1);

-- 本年
SELECT * FROM t 
WHERE date_col >= TRUNC(SYSDATE, 'YYYY') 
  AND date_col < ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12);

-- 最近 N 天
SELECT * FROM t WHERE date_col >= SYSDATE - 7;

-- 某年
SELECT * FROM t WHERE EXTRACT(YEAR FROM date_col) = 2025;
-- 或索引友好
SELECT * FROM t 
WHERE date_col >= DATE '2025-01-01' AND date_col < DATE '2026-01-01';

7.2 范围

-- 日期范围
SELECT * FROM t 
WHERE date_col BETWEEN DATE '2025-01-01' AND DATE '2025-12-31';

7.3 年龄

SELECT 
  name,
  birth_date,
  FLOOR(MONTHS_BETWEEN(SYSDATE, birth_date) / 12) AS age_years
FROM employees;

---

8. 性能

8.1 索引

-- 差（函数阻止索引）
SELECT * FROM t WHERE TO_CHAR(date_col, 'YYYY-MM-DD') = '2025-07-21';

-- 好
SELECT * FROM t WHERE date_col = TO_DATE('2025-07-21', 'YYYY-MM-DD');

-- 范围更好
SELECT * FROM t 
WHERE date_col >= TO_DATE('2025-07-21 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
  AND date_col < TO_DATE('2025-07-22 00:00:00', 'YYYY-MM-DD HH24:MI:SS');

8.2 分区

CREATE TABLE sales (...) PARTITION BY RANGE (sale_date) (...);

详细见：Oracle 表分区策略详解。

---

9. 常见坑与排错

9.1 ORA-01843

- 无效月份
- 格式

9.2 ORA-01858

- 非数字字符
- 格式

9.3 ORA-01830

- 日期格式结尾
- 截断

9.4 闰年

SELECT TO_DATE('2024-02-29', 'YYYY-MM-DD') FROM dual;  -- OK
SELECT TO_DATE('2025-02-29', 'YYYY-MM-DD') FROM dual;  -- ERROR

9.5 时区

- TIMESTAMP WITH TIME ZONE
- 转换
- 显示

---

10. NLS

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
ALTER SESSION SET NLS_TIME_FORMAT = 'HH24:MI:SS.FF';

SELECT * FROM nls_session_parameters;

---

11. 最佳实践

1. TIMESTAMP：现代
2. 时区：WITH TIME ZONE
3. 范围查询：索引
4. 避免函数：索引
5. EXTRACT：提取
6. TRUNC：截断
7. INTERVAL：计算
8. NLS：格式
9. 分区：历史
10. 测试：验证

---

12. 参考资料

[1] Oracle Database SQL Language Reference 19c, “Datetime” https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Datetime-Data-Types-and-Time-Zone-Support.html