【Oracle】日期类型理解

见脚本：

-- Create table
create table T_STUDY_TIME
(
  id         VARCHAR2(32),
  date1      DATE,
  date2      DATE,
  timestamp1 TIMESTAMP(6),
  timestamp2 TIMESTAMP(6)
);

-- select
SELECT * FROM T_STUDY_TIME;

-- insert
INSERT INTO T_STUDY_TIME
  (ID, DATE1, DATE2, TIMESTAMP1, TIMESTAMP2)
VALUES
  (sys_guid(),
   TO_DATE('10-06-2017 12:40:27', 'dd-mm-yyyy hh24:mi:ss'),
   TO_DATE('14-08-2017 13:57:55', 'dd-mm-yyyy hh24:mi:ss'),
   to_timestamp('2017-06-10 12:40:27.136500','yyyy-mm-dd hh24:mi:ss.ff6'),
   '14-8月 -17 01.57.55.164936 下午');
------------------------------------------------------
SELECT ID,
       DATE1,
       DATE2,
       TO_CHAR(DATE1, 'YYYY/MM/DD HH24:MI:SS') TO_CHAR_DATE1,
       TO_CHAR(DATE2, 'YYYY/MM/DD HH24:MI:SS') TO_CHAR_DATE2,
       NULL,
       TRUNC((DATE2 - DATE1) * 24 * 60 * 60) TOTAL_SECONDS, -- 相差总秒数，精确到秒
       (TRUNC((DATE2 - DATE1) * 24 * 60)) * 60,
       (TRUNC((DATE2 - DATE1) * 24 * 60 * 60)) -
       ((TRUNC((DATE2 - DATE1) * 24 * 60)) * 60) SECONDS, -- 秒数位差
       NULL,
       TRUNC((DATE2 - DATE1) * 24 * 60) TOTAL_MINUTES, -- 相差总分钟数，精确到分钟
       (TRUNC((DATE2 - DATE1) * 24)) * 60,
       (TRUNC((DATE2 - DATE1) * 24 * 60)) -
       ((TRUNC((DATE2 - DATE1) * 24)) * 60) MINUTES, -- 分数位差
       NULL,
       TRUNC((DATE2 - DATE1) * 24) TOTAL_HOURS, -- 相差总小时数，精确到小时
       TRUNC(DATE2 - DATE1) * 24,
       (TRUNC((DATE2 - DATE1) * 24)) - ((TRUNC(DATE2 - DATE1) * 24)) HOURS, -- 小时数位差
       NULL,
       DATE2 - DATE1 TOTAL_DAYS, -- 相差总天数
       TRUNC(DATE2 - DATE1) DAYS, -- 天数位差
       SYSDATE,
       SYSDATE + 1, -- 加1天
       NULL,
       TIMESTAMP1,
       TIMESTAMP2,
       TO_CHAR(TIMESTAMP1, 'YYYY/MM/DD HH24:MI:SS.FF6') TO_CHAR_TIMESTAMP1,
       TO_CHAR(TIMESTAMP2, 'YYYY/MM/DD HH24:MI:SS.FF6') TO_CHAR_TIMESTAMP2,
       TIMESTAMP2 - TIMESTAMP1,
       SYSTIMESTAMP,
       SYSTIMESTAMP + 1
  FROM T_STUDY_TIME
 WHERE ID = '56B2872E03615253E05310DC810A4194';

查询结果：