日期操作

本章节主要介绍在 DM 数据库中如何实现各种日期相关的运算。

一、适用场景

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

二、操作方法

2.1 SYSDATE

使用 trunc() 函数获得某个日期对应的月初时间。示例语句如下所示：

SELECT employee_name,
       hire_date AS 入职日期,
       TRUNC (hire_date, 'mm') AS 月初
  FROM dmhr.employee
 WHERE ROWNUM <= 1;

输出结果：

下面列举几个常用的取值方式，求具体时间对应的时分秒、日月年及周初、月初等。

SELECT hire_date,
       TO_NUMBER (TO_CHAR (hire_date, 'hh24')) 时,
       TO_NUMBER (TO_CHAR (hire_date, 'mi')) 分,
       TO_NUMBER (TO_CHAR (hire_date, 'ss')) 秒,
       TO_NUMBER (TO_CHAR (hire_date, 'dd')) 日,
       TO_NUMBER (TO_CHAR (hire_date, 'mm')) 月,
       TO_NUMBER (TO_CHAR (hire_date, 'yyyy')) 年,
       TO_NUMBER (TO_CHAR (hire_date, 'ddd')) 年内第几天,
       TRUNC (hire_date, 'dd') 一天之始,
       TRUNC (hire_date, 'day') 周初,
       TRUNC (hire_date, 'mm') 月初,
       LAST_DAY (hire_date) 月末,
       ADD_MONTHS (TRUNC (hire_date, 'mm'), 1) 下月初,
       TRUNC (hire_date, 'yy') 年初,
       TO_CHAR (hire_date, 'day') 周几,
       TO_CHAR (hire_date, 'month') 月份
  FROM (SELECT hire_date + 30 / 24 / 60 / 60 + 20 / 24 / 60 + 5 / 24
                  AS hire_date
          FROM dmhr.employee
         WHERE ROWNUM <= 1);

2.2 EXTRACT

EXTRACT 函数可以提取时间字段中的年、月、日、时、分、秒，返回的值为 NUMBER 类型。示例语句如下所示：

SELECT EXTRACT (YEAR FROM SYSTIMESTAMP) AS "YEAR",
       EXTRACT (MONTH FROM SYSTIMESTAMP) AS "MONTH",
       EXTRACT (DAY FROM SYSTIMESTAMP) AS "DAY",
       EXTRACT (HOUR FROM SYSTIMESTAMP) AS "HOUR",
       EXTRACT (MINUTE FROM SYSTIMESTAMP) AS "MINUTE",
       EXTRACT (SECOND FROM SYSTIMESTAMP) AS "SECOND"
  FROM DUAL;

输出结果：

to_char 函数可以用来取日期时间类型字段中的时、分、秒。示例语句如下所示：

SELECT created, TO_CHAR (created, 'dd') AS d, TO_CHAR (created, 'hh24') AS h
  FROM dba_objects
 WHERE object_id = 2;

输出结果：

EXTRACT 函数可以用来取 INTERVAL 中的信息，to_char 函数不支持。

• EXTRACT 函数实现

示例语句如下所示：

SELECT EXTRACT (HOUR FROM it) AS "hour"
  FROM (SELECT INTERVAL '2 12:30:59' DAY TO SECOND AS it FROM DUAL);

输出结果：

• TO_CHAR 函数无法实现

示例语句如下所示：

SELECT TO_CHAR (it, 'hh24') AS "hour"
  FROM (SELECT INTERVAL '2 12:30:59' DAY TO SECOND AS it FROM DUAL);

输出结果：

2.3 判断一年是否为闰年

判断一年是否为闰年，可以看二月的月末具体是哪一天。使用 TO_CHAR、 LAST_DAY、 ADD_MONTHS、 TRUNC 函数共同实现。示例语句如下所示：

--计算年初
SELECT TRUNC (hire_date, 'y') 年初
  FROM dmhr.employee
 WHERE ROWNUM <= 1;

--计算二月初
SELECT ADD_MONTHS (TRUNC (hire_date, 'y'), 1) 二月初
  FROM dmhr.employee
 WHERE ROWNUM <= 1;

--计算二月底
SELECT LAST_DAY (ADD_MONTHS (TRUNC (hire_date, 'y'), 1)) AS 二月底
  FROM dmhr.employee
 WHERE ROWNUM <= 1;

 --计算二月底对应的日期
SELECT TO_CHAR (LAST_DAY (ADD_MONTHS (TRUNC (hire_date, 'y'), 1)), 'DD')
          AS 日
  FROM dmhr.employee
 WHERE ROWNUM <= 1;

输出结果：

2.4 周的计算

使用 TO_CHAR、 NEXT_DAY、TRUNC 函数共同实现周的计算。示例语句如下所示：

WITH x
     AS (SELECT TRUNC (SYSDATE, 'yy') + (LEVEL - 1) AS 日期
               FROM DUAL
         CONNECT BY LEVEL <= 8)
SELECT 日期,
       TO_CHAR (日期, 'd') AS d,
       TO_CHAR (日期, 'day') AS day,
       NEXT_DAY (日期, 1) AS 下个周日,
       TO_CHAR (日期, 'ww') AS ww,
       TO_CHAR (日期, 'iw') AS iw
  FROM x;

输出结果：

注意

参数 “day” 与字符集无关，所以建议使用 “d”。 WW 与 IW 都是取 “第几周”，只是两个参数的初始值不一样。

2.5 确定一年内属于周内某一天的所有日期

本例要求返回指定年份内的所有周五对应的日期。示例语句如下所示：

WITH x
     AS (SELECT TRUNC (SYSDATE, 'yy') + (LEVEL - 1) dy
               FROM DUAL
         CONNECT BY LEVEL <=
                         ADD_MONTHS (TRUNC (SYSDATE, 'y'), 12)
                       - TRUNC (SYSDATE, 'y'))
SELECT dy, TO_CHAR (dy, 'day') AS 周五
  FROM x
 WHERE TO_CHAR (dy, 'd') = 6;

输出结果：

2.6 确定某月内第一个和最后一个周末某天的日期

本例要求返回当月内第一个星期一与最后一个星期一，我们分别找上月某及当月末之前七天的下一周的周一即可。示例语句如下所示：

SELECT NEXT_DAY (TRUNC (hire_date, 'mm') - 1, 2) 第一个周一,
       NEXT_DAY (LAST_DAY (TRUNC (hire_date, 'mm')) - 7, 2)
          最后一个周一
  FROM dmhr.employee
 WHERE ROWNUM <= 1;

输出结果：

2.7 创建本月日历

枚举指定月份所有的日期，并转换为对应的周信息，再按所在周做一次 行转列 即可。示例语句如下所示：

WITH x1
   /*给定一个日期*/
     AS (SELECT TO_DATE ('2020-11-01', 'yyyy-mm-dd') AS cur_date FROM DUAL),
     x2
    /*取月初*/
     AS (SELECT TRUNC (cur_date, 'mm') AS 月初,
                ADD_MONTHS (TRUNC (cur_date, 'mm'), 1) AS 下月初
           FROM x1),
     x3
    /*枚举当月所有天*/
     AS (SELECT 月初 + (LEVEL - 1) AS 日
               FROM x2
         CONNECT BY LEVEL <= (下月初 - 月初)),
     x4
     /*提取周信息*/
     AS (SELECT TO_CHAR (日, 'iw') 所在周,
                TO_CHAR (日, 'dd') 日期,
                TO_NUMBER (TO_CHAR (日, 'd')) 周几
           FROM x3)
  SELECT MAX (CASE 周几 WHEN 2 THEN 日期 END) 周一,
         MAX (CASE 周几 WHEN 3 THEN 日期 END) 周二,
         MAX (CASE 周几 WHEN 4 THEN 日期 END) 周三,
         MAX (CASE 周几 WHEN 5 THEN 日期 END) 周四,
         MAX (CASE 周几 WHEN 6 THEN 日期 END) 周五,
         MAX (CASE 周几 WHEN 7 THEN 日期 END) 周六,
         MAX (CASE 周几 WHEN 1 THEN 日期 END) 周日
    FROM x4
GROUP BY 所在周
ORDER BY 所在周;

输出结果：

2.8 确定指定年份季度的开始日期和结束日期

在写报表查询语句时需要按季度分类汇总，提取对应的季度开始日期和结束日期。可以通过 add_months、to_date 函数实现。示例语句如下所示：

SELECT sn AS 季度,
       (sn - 1) * 3 + 1 AS 开始月份,
       ADD_MONTHS (TO_DATE (年, 'yyyy'), (sn - 1) * 3) AS 开始日期,
       ADD_MONTHS (TO_DATE (年, 'yyyy'), sn * 3) - 1 AS 结束日期
  FROM (SELECT '2020' AS 年, LEVEL AS sn
              FROM DUAL
        CONNECT BY LEVEL <= 4);

输出结果：

2.9 补充范围内丢失的值

本例需要统计每一年份入职员工数，若表中没有的年份，则展示的统计人数为 0。补充范围内丢失的值，可以按如下 2 个步骤完成：

• 生成年份枚举列表

示例语句如下所示：

WITH x
     AS (SELECT 开始年份 + (LEVEL - 1) AS 年份
               FROM (SELECT EXTRACT (YEAR FROM MIN (hire_date)) AS 开始年份,
                            EXTRACT (YEAR FROM MAX (hire_date)) AS 结束年份
                       FROM dmhr.employee)
         CONNECT BY LEVEL <= 结束年份 - 开始年份 + 1)
SELECT * FROM x;

输出结果：

• 关联查询得到结果集

示例语句如下所示：

WITH x
     AS (SELECT 开始年份 + (LEVEL - 1) AS 年份
               FROM (SELECT EXTRACT (YEAR FROM MIN (hire_date)) AS 开始年份,
                            EXTRACT (YEAR FROM MAX (hire_date)) AS 结束年份
                       FROM dmhr.employee)
         CONNECT BY LEVEL <= 结束年份 - 开始年份 + 1)
  SELECT x.年份, COUNT (e.employee_id) 入职人数
    FROM x
         LEFT JOIN dmhr.employee e
            ON (EXTRACT (YEAR FROM e.hire_date) = x.年份)
GROUP BY x.年份
ORDER BY 1;

输出结果：

2.10 按照给定的时间单位查找

使用 to_char 函数查询给定时间单位的时间。比如查询如入职日期在 1 月或者 12 月且非星期三的员工信息。示例语句如下所示：

SELECT employee_name 姓名,
       hire_date 入职日期,
       TO_CHAR (hire_date, 'day') AS 星期
  FROM dmhr.employee
 WHERE     TO_CHAR (hire_date, 'mm') IN ('01', '12')
       AND TO_CHAR (hire_date, 'd') != '4';

输出结果：

2.11 使用日期的特殊部分比较记录

使用 to_char 函数统计相同月份与周内日期入职的员工。示例语句如下所示：

SELECT employee_name 姓名,
       hire_date 入职日期,
       TO_CHAR (hire_date, 'MON day') AS 月周
  FROM (SELECT employee_name,
               hire_date,
               COUNT (*) OVER (PARTITION BY TO_CHAR (hire_date, 'MON day'))
                  AS ct
          FROM dmhr.employee)
 WHERE hire_date LIKE '2015%';

输出结果：

三、参考文献

更多 SQL 语言使用说明，请参考《DM8_SQL 语言使用手册》，手册位于数据库安装路径 /dmdbms/doc 文件夹下。如有其他问题，请在社区内咨询。