本章节主要介绍在 DM 数据库中如何进行日期运算。
一、适用场景
| 软件 | 版本 |
|---|---|
| 操作系统 | Redhat 7 及以上版本 |
| DM 数据库 | DM 8.0 及以上版本 |
| CPU 架构 | x86、ARM、龙芯、飞腾等国内外主流 CPU |
二、操作方法
2.1 加减日、月、年
date 类型的数据可以直接加减天数，加减月份需要使用 add_months 函数，同时也可以使用 add_days 加减天数。示例语句如下所示：
--根据某个员工的入职日计算其前五天、后五天、前五个月、后五个月、前五年及后5年的具体时间
SELECT hire_date AS 聘用日期,
add_days (hire_date, -5) AS 减5天,
add_days (hire_date, 5) AS 加5天,
add_months (hire_date, -5) AS 减5月,
add_months (hire_date, 5) AS 加5月,
add_months (hire_date, -5 * 12) AS 减5年,
add_months (hire_date, 5 * 12) AS 加5年
FROM dmhr.employee
WHERE ROWNUM <= 1;
输出结果：
2.2 加减时、分、秒
时间类型的数据可以直接加减时、分、秒，1/24 指的是 1 小时。示例语句如下所示：
select sysdate as 当前日期,
sysdate - 5.0 / 24 as 减5小时,
sysdate + 5.0 / 24 as 加5小时,
sysdate - 5.0 / 24 / 60 as 减5分钟,
sysdate + 5.0 / 24 / 60 as 加5分钟
from dual;
输出结果：
2.3 日期间隔之时、分、秒
两个 date 相减，得到的是天数，乘以 24 即为小时，以此类推可计算出秒。示例语句如下所示：
SELECT 间隔天数,
间隔天数 * 24 AS 间隔小时,
间隔天数 * 24 * 60 AS 间隔分,
间隔天数 * 24 * 60 * 60 AS 间隔秒
FROM (SELECT MAX (hire_date) - MIN (hire_date) AS 间隔天数
FROM dmhr.employee
WHERE employee_name IN ('马学铭', '陈仙')) x;
输出结果：
2.4 日期间隔之日、月、年
使用 months_between 函数计算间隔月份，以此类推计算出间隔年。示例语句如下所示：
SELECT max_hd - min_hd 间隔天,
MONTHS_BETWEEN (max_hd, min_hd) 间隔月,
MONTHS_BETWEEN (max_hd, min_hd) / 12 间隔年
FROM (SELECT MAX (hire_date) max_hd, MIN (hire_date) min_hd
FROM dmhr.employee);
输出结果：
2.5 求两个日期间的工作天数
本例要求返回员工“马学铭”，“陈仙”聘用日期之间的工作天数。
- 创建 t500 表，示例语句如下所示：
CREATE TABLE dmhr.T500 AS SELECT LEVEL AS ID FROM dual CONNECT BY LEVEL <= 500;
- 原始数据，示例语句如下所示：
SELECT employee_name, hire_date
FROM dmhr.employee
WHERE employee_name IN ('马学铭', '陈仙');
输出结果：
- 通过
MAX()将原数据转为一行，示例语句如下所示：
SELECT MIN (hire_date) AS min_hd, MAX (hire_date) AS max_hd
FROM dmhr.employee
WHERE employee_name IN ('马学铭', '陈仙');
输出结果：
- 枚举 2 个日期之间的天数，示例语句如下所示：
SELECT (max_hd - min_hd) + 1 AS 天数
FROM (SELECT MIN (hire_date) AS min_hd, MAX (hire_date) AS max_hd
FROM dmhr.employee
WHERE employee_name IN ('马学铭', '陈仙')) x;
输出结果：
- 与 T500 做笛卡尔积枚举 30 天的所有日期，示例语句如下所示：
SELECT min_hd + (dmhr.t500.id - 1) AS 日期
FROM (SELECT MIN (hire_date) AS min_hd, MAX (hire_date) AS max_hd
FROM dmhr.employee
WHERE employee_name IN ('马学铭', '陈仙')) x,
dmhr.t500
WHERE dmhr.t500.id <= ( (max_hd - min_hd) + 1);
输出结果：
- 根据日期得到对应的工作日信息，示例语句如下所示：
SELECT 日期, TO_CHAR (日期, 'DY', 'NLS_DATE_LANGUAGE = American') AS dy
FROM (SELECT min_hd + (dmhr.t500.id - 1) AS 日期
FROM (SELECT MIN (hire_date) AS min_hd, MAX (hire_date) AS max_hd
FROM dmhr.employee
WHERE employee_name IN ('马学铭', '陈仙')) x,
dmhr.t500
WHERE dmhr.t500.id <= ( (max_hd - min_hd) + 1));
输出结果：
- 过滤并汇总，示例语句如下所示：
SELECT COUNT (*)
FROM (SELECT 日期,
TO_CHAR (日期, 'DY', 'NLS_DATE_LANGUAGE = American') AS dy
FROM (SELECT min_hd + (dmhr.t500.id - 1) AS 日期
FROM (SELECT MIN (hire_date) AS min_hd,
MAX (hire_date) AS max_hd
FROM dmhr.employee
WHERE employee_name IN ('马学铭', '陈仙')) x,
dmhr.t500
WHERE dmhr.t500.id <= ((max_hd - min_hd) + 1)))
WHERE dy NOT IN ('SAT', 'SUN');
输出结果：
2.6 求一年中周内各日期的天数
比如，计算一年内有多少天是星期一，多少天是星期二等，可以按照如下步骤实现：
- 取得大当前年度信息。
- 计算一年有多少天。
- 生成日期列表。
- 转换为对应的星期标识。
- 汇总统计。
示例语句如下所示：
WITH x0 AS (SELECT TO_DATE ('2020-01-01', 'yyyy-mm-dd') AS 年初 FROM DUAL),
x1 AS (SELECT 年初, ADD_MONTHS (年初, 12) AS 下年初 FROM x0),
x2 AS (SELECT 年初, 下年初, 下年初 - 年初 AS 天数 FROM x1),
x3
AS (SELECT 年初 + (LEVEL - 1) AS 日期
FROM x2
CONNECT BY LEVEL <= 天数),
x4 AS (SELECT 日期, TO_CHAR (日期, 'DY') AS 星期 FROM x3)
SELECT 星期, COUNT (*) AS 天数
FROM x4
GROUP BY 星期;
输出结果：
2.7 确定当前记录和下一条记录之间相差的天数
- 使用
lead() over()分析函数，将下一条记录的雇佣日期作为当前行，示例语句如下所示：
SELECT employee_id,employee_name,hire_date,
LEAD (hire_date) OVER (ORDER BY hire_date) next_hd
FROM dmhr.employee
WHERE job_id = 11;
输出结果：
- 统计数据，示例语句如下所示：
SELECT employee_name,hire_date,next_hd,next_hd - hire_date diff
FROM (SELECT employee_id,employee_name,hire_date,
LEAD (hire_date) OVER (ORDER BY hire_date) next_hd
FROM dmhr.employee
WHERE job_id = 11);
输出结果：
三、参考文献
更多 SQL 语言使用说明，请参考《DM8_SQL 语言使用手册》，手册位于数据库安装路径 /dmdbms/doc 文件夹下。如有其他问题，请在社区内咨询。
