Oracle日期时间函数说明及与MySql区别说明

Oracle 数据库中时间类型主要包括 DATE 和 TIMESTAMP，其中 DATE 是最常用的日期类型，它可以保存日期和时间，常用日期处理都可以采用这种类型。DATE 表示的日期范围可以是公元前4712年1月1日至公元9999年12月31日。DATE 类型在数据库中的存储固定为7个字节，分别表示世纪、年、月、天、小时、分和秒。

时间函数使用说明
TO_DATE 函数
TO_DATE() 函数用于将字符串转换为 DATE 类型。括号中可以是字符串，但该字符串必须是满足时间格式的，并且要写上对应的时间格式样式，不然解析字符串失败。例如：

SELECT TO_DATE('2023-10-01', 'YYYY-MM-DD') FROM dual;

如果输入的是时间类型的数据，则不能再写时间格式样式，否则会报错解析不了。

TO_CHAR 函数
TO_CHAR() 函数用于将 DATE 或 TIMESTAMP 类型转换为字符串。括号中可以是时间类型的数据，可以写时间格式样式，也可以不写，不写时间格式样式就会使用 Oracle 中默认的时间格式样式来转化。例如：

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;

SYSDATE 函数
SYSDATE 函数返回当前系统的日期和时间。例如：

SELECT SYSDATE FROM dual;

ADD_MONTHS 函数
ADD_MONTHS(date, n) 函数用于给指定的日期加上 n 个月。例如：

SELECT ADD_MONTHS(TO_DATE('2023-10-01', 'YYYY-MM-DD'), 1) FROM dual;

LAST_DAY 函数
LAST_DAY(date) 函数用于返回指定日期所在月份的最后一天。例如：

SELECT LAST_DAY(TO_DATE('2023-10-01', 'YYYY-MM-DD')) FROM dual;

NEXT_DAY 函数
NEXT_DAY(date, char) 函数用于返回指定日期之后的下一个星期几。例如：

SELECT NEXT_DAY(TO_DATE('2023-10-01', 'YYYY-MM-DD'), 'MONDAY') FROM dual;

TRUNC 函数
TRUNC(date, format) 函数用于截断日期。例如：

SELECT TRUNC(SYSDATE, 'MONTH') FROM dual; -- 返回当月的第一天

ROUND 函数
ROUND(date, format) 函数用于四舍五入日期。例如：

 SELECT ROUND(SYSDATE, 'MONTH') FROM dual; -- 返回最近的月份的第一天

查询方式
Oracle 数据库日期范围查询有两种方式：TO_CHAR 方式和 TO_DATE 方式。可以通过 TO_CHAR 处理后的字符串类型的时间进行时间的筛选，也可以通过 TO_DATE 处理后的时间类型的时间进行筛选3。

示例代码

-- 使用 TO_DATE 转换字符串为 DATE 类型
SELECT * FROM employees WHERE hire_date > TO_DATE('2020-01-01', 'YYYY-MM-DD');-- 使用 TO_CHAR 将 DATE 类型转换为字符串
SELECT employee_id, TO_CHAR(hire_date, 'YYYY-MM-DD') AS hire_date_str FROM employees;-- 获取当前系统日期
SELECT SYSDATE FROM dual;-- 添加一个月到指定日期
SELECT ADD_MONTHS(TO_DATE('2023-10-01', 'YYYY-MM-DD'), 1) FROM dual;-- 获取指定日期所在月份的最后一天
SELECT LAST_DAY(TO_DATE('2023-10-01', 'YYYY-MM-DD')) FROM dual;-- 获取指定日期之后的下一个星期一
SELECT NEXT_DAY(TO_DATE('2023-10-01', 'YYYY-MM-DD'), 'MONDAY') FROM dual;-- 截断日期到月份的第一天
SELECT TRUNC(SYSDATE, 'MONTH') FROM dual;-- 四舍五入日期到最近的月份的第一天
SELECT ROUND(SYSDATE, 'MONTH') FROM dual;

Oracle 和 MySQL 时间函数的区别

Oracle 和 MySQL 在时间函数的使用上存在一定的差异，主要体现在函数名称、参数格式以及功能支持等方面。以下是对两者时间函数的比较分析：

1. 获取当前系统时间
   Oracle 使用 SYSDATE 函数获取当前系统的日期和时间，如果需要更高精度的时间（包括时区信息），可以使用 SYSTIMESTAMP 函数1。

MySQL 使用 NOW() 或 CURRENT_TIMESTAMP 函数来获取当前的日期和时间。

-- Oracle 获取当前时间
SELECT SYSDATE FROM dual;-- MySQL 获取当前时间
SELECT NOW();

2. 字符串与日期之间的转换
   Oracle 提供了 TO_DATE() 和 TO_CHAR() 函数用于将字符串转换为日期类型或将日期转换为字符串。例如：

-- Oracle 将字符串转换为日期
SELECT TO_DATE('2023-10-01', 'YYYY-MM-DD') FROM dual;-- Oracle 将日期转换为字符串
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;

MySQL 使用 STR_TO_DATE() 函数将字符串转换为日期，使用 DATE_FORMAT() 函数将日期格式化为字符串。例如：

 -- MySQL 将字符串转换为日期
SELECT STR_TO_DATE('2023-10-01', '%Y-%m-%d');-- MySQL 将日期转换为字符串
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

3. 日期加减操作
   Oracle 使用 ADD_MONTHS(date, n) 函数对日期进行月份级别的加法运算，也可以通过直接加减整数实现天数的增减。例如：

 -- Oracle 添加一个月
SELECT ADD_MONTHS(TO_DATE('2023-10-01', 'YYYY-MM-DD'), 1) FROM dual;-- Oracle 添加一天
SELECT TO_DATE('2023-10-01', 'YYYY-MM-DD') + 1 FROM dual;

MySQL 使用 DATE_ADD() 或 DATE_SUB() 函数来进行日期加减操作。例如：

-- MySQL 添加一个月
SELECT DATE_ADD('2023-10-01', INTERVAL 1 MONTH);-- MySQL 添加一天
SELECT DATE_ADD('2023-10-01', INTERVAL 1 DAY);

4. 月末和周几计算
   Oracle 提供了 LAST_DAY(date) 函数返回指定日期所在月份的最后一天，NEXT_DAY(date, char) 返回下一个星期几2。例如：

-- Oracle 获取月末
SELECT LAST_DAY(TO_DATE('2023-10-01', 'YYYY-MM-DD')) FROM dual;-- Oracle 获取下一个星期一
SELECT NEXT_DAY(TO_DATE('2023-10-01', 'YYYY-MM-DD'), 'MONDAY') FROM dual;

MySQL 没有直接等价于 LAST_DAY() 和 NEXT_DAY() 的函数，但可以通过组合其他函数实现类似的功能。例如：

 -- MySQL 获取月末
SELECT LAST_DAY('2023-10-01');-- MySQL 获取下一个星期一
SELECT DATE_ADD('2023-10-01', INTERVAL (8 - WEEKDAY('2023-10-01')) % 7 DAY);

5. 截断和四舍五入日期
   Oracle 使用 TRUNC(date, format) 和 ROUND(date, format) 分别实现日期截断和四舍五入。例如：

 -- Oracle 截断日期到月份的第一天
SELECT TRUNC(SYSDATE, 'MONTH') FROM dual;-- Oracle 四舍五入日期到最近的月份的第一天
SELECT ROUND(SYSDATE, 'MONTH') FROM dual;MySQL 没有直接的 TRUNC() 或 ROUND() 函数处理日期，但可以通过 DATE_FORMAT() 和 STR_TO_DATE() 组合实现类似效果。例如：-- MySQL 截断日期到月份的第一天
SELECT STR_TO_DATE(DATE_FORMAT(NOW(), '%Y-%m-01'), '%Y-%m-%d');

6. 日期差值计算
   Oracle 可以通过直接相减两个日期得到天数差，或者使用 MONTHS_BETWEEN(date1, date2) 计算月份数差。例如：

 -- Oracle 计算两个日期之间的天数差
SELECT TO_DATE('2023-10-02', 'YYYY-MM-DD') - TO_DATE('2023-10-01', 'YYYY-MM-DD') FROM dual;-- Oracle 计算两个日期之间的月份数差
SELECT MONTHS_BETWEEN(TO_DATE('2023-11-01', 'YYYY-MM-DD'), TO_DATE('2023-10-01', 'YYYY-MM-DD')) FROM dual;

MySQL 使用 DATEDIFF(date1, date2) 计算天数差，使用 TIMESTAMPDIFF(unit, date1, date2) 计算不同单位的时间差。例如：

-- MySQL 计算两个日期之间的天数差
SELECT DATEDIFF('2023-10-02', '2023-10-01');-- MySQL 计算两个日期之间的月份数差
SELECT TIMESTAMPDIFF(MONTH, '2023-10-01', '2023-11-01');