日期运算

本章节主要介绍在 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;

输出结果:

加减日月年

加减时、分、秒

时间类型的数据可以直接加减时、分、秒,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;

输出结果:

image.png

日期间隔之时、分、秒

两个 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 文件夹下。如有其他问题,请在社区内咨询。

微信扫码
分享文档
扫一扫
联系客服