DM8达梦数据库日期运算

本章节主要介绍在 DM 数据库中如何进行日期运算。

适用场景

软件	版本
操作系统	Redhat 7 及以上版本
DM 数据库	DM 8.0 及以上版本
CPU 架构	x86、ARM、龙芯、飞腾等国内外主流 CPU

操作方法

加减日、月、年

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;

输出结果：

加减日月年

加减时、分、秒

date 类型的数据可以直接加减天数，1/24 指的是 1 小时。示例语句如下所示：

SELECT hire_date AS 聘用日期,
       hire_date - 5 / 24 AS 减5小时,
       hire_date - 5 / 24 AS 加5小时,
       hire_date - 5 / 24 / 24 AS 减5分,
       hire_date - 5 / 24 / 24 AS 加5分
  FROM dmhr.employee
 WHERE ROWNUM <= 1;

输出结果：

加减十分秒

日期间隔之时、分、秒

两个 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;

输出结果：

间隔十分秒

日期间隔之日、月、年

使用 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);

输出结果：

间隔日月年

求两个日期间的工作天数

本例要求返回员工“马学铭”，“陈仙”聘用日期之间的工作天数。

创建 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');

输出结果：

汇总信息

求一年中周内各日期的天数

比如，计算一年内有多少天是星期一，多少天是星期二等，可以按照如下步骤实现：

取得大当前年度信息。
计算一年有多少天。
生成日期列表。
转换为对应的星期标识。
汇总统计。

示例语句如下所示：

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 星期;

输出结果：

一年中周内各日期的天数

确定当前记录和下一条记录之间相差的天数

使用 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;

输出结果：

lead over分析函数

统计数据，示例语句如下所示：

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 语言使用说明，请参考《DM_SQL 语言使用手册》，手册位于数据库安装路径 /dmdbms/doc 文件夹下。如有其他问题，请在社区内咨询。

DM8达梦数据库日期运算

适用场景

操作方法

加减日、月、年

加减时、分、秒

日期间隔之时、分、秒

日期间隔之日、月、年

求两个日期间的工作天数

求一年中周内各日期的天数

确定当前记录和下一条记录之间相差的天数

参考文献

评论