见脚本:
-- 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';
查询结果: