Oracle 日期类型timestamp(时间戳)和date类型使用

DATE数据类型

可以存储月，年，日，世纪，时，分和秒。度量粒度是秒

以使用TO_CHAR函数把DATE数据进行传统地包装，达到表示成多种格式的目的

SELECT TO_CHAR(sysdate,'MM/DD/YYYY HH24:MI:SS') "Date" FROM dual

大多数人陷入的麻烦就是计算两个时间间的间隔年数、月数、天数、小时数和秒数。你需要明白的是，当你进行两个日期的相减运算的时候，得到的是“带小数位的天数”。

可以通过一个函数将天数转换成“天时分秒”格式：

CREATE OR REPLACE FUNCTION F_DAYS2STR(P_DAYS IN NUMBER DEFAULT 0)     RETURN VARCHAR2 IS     --Ver:1.0     --Created by xsb on 2005-05-26     --For: 将天数转换成天时分秒格式     DAYS   NUMBER := NVL(P_DAYS, 0);     VD     NUMBER; --天     VH     NUMBER; --小时     VM     NUMBER; --分     VS     NUMBER; --秒     RESULT VARCHAR2(100); --返回值 BEGIN     VD := TRUNC(DAYS);     VH := TRUNC((DAYS - VD) * 24);     VM := TRUNC((DAYS - VD - VH / 24) * 24 * 60);     VS := TRUNC((DAYS - VD - VH / 24 - VM / 24 / 60) * 24 * 60 * 60);     SELECT DECODE(VD, 0, '', VD || '天') || DECODE(VH, 0, '', VH || '小时') ||DECODE(VM, 0, '', VM || '分') || DECODE(VS, 0, '', VS ||'秒')   INTO RESULT FROM DUAL;     RETURN(RESULT); END;

TIMESTAMP 数据类型

它包括了所有DATE数据类型的年月日时分秒的信息，而且包括了小数秒的信息。

SELECT sysdate,systimestamp FROM dual

注意，to_char函数支持date和timestamp，但是trunc却不支持TIMESTAMP数据类型。

SELECT sysdate,systimestamp,to_char(systimestamp,'yyyymmdd hh24:mi:ssxff3') FROM dual

当你直接相减的话，看看会发生什么。结果将更容易理解，

select systimestamp-systimestamp from dual

这就意味着不用再通过麻烦的计算求出多少天多少秒，用substr函数摘取出其中的数字即可！

结论：当使用date和timestamp类型的时候，选择是很清楚的。你可以随意处置date和timestamp类型。当你的时间粒度必须精确至秒以下时，可以考虑使用timestamp。

再来看如何实现：将天数转换成“天时分秒”格式：

使用DATE数据类型：

create table t2 (d1 date,d2 date) ;INSERT INTO T2 VALUES (SYSDATE, SYSDATE + 1.1234);SELECT D1, D2, D2 - D1, F_DAYS2STR(D2 - D1),             CAST(D2 AS TIMESTAMP) - CAST(D1 AS TIMESTAMP)     FROM T2

SELECT TO_NUMBER(SUBSTR(D21, 1, INSTR(D21, ' '))) D,             SUBSTR(D21, INSTR(D21, ' ') + 1, 2) H,             SUBSTR(D21, INSTR(D21, ' ') + 4, 2) M,             SUBSTR(D21, INSTR(D21, ' ') + 7, 2) S     FROM (SELECT CAST(D2 AS TIMESTAMP) - CAST(D1 AS TIMESTAMP) D21            FROM T2)DATE数据类型

可以存储月，年，日，世纪，时，分和秒。度量粒度是秒

以使用TO_CHAR函数把DATE数据进行传统地包装，达到表示成多种格式的目的

SELECT TO_CHAR(sysdate,'MM/DD/YYYY HH24:MI:SS') "Date" FROM dual

大多数人陷入的麻烦就是计算两个时间间的间隔年数、月数、天数、小时数和秒数。你需要明白的是，当你进行两个日期的相减运算的时候，得到的是“带小数位的天数”。

可以通过一个函数将天数转换成“天时分秒”格式：

CREATE OR REPLACE FUNCTION F_DAYS2STR(P_DAYS IN NUMBER DEFAULT 0)     RETURN VARCHAR2 IS     --Ver:1.0     --Created by xsb on 2005-05-26     --For: 将天数转换成天时分秒格式     DAYS   NUMBER := NVL(P_DAYS, 0);     VD     NUMBER; --天     VH     NUMBER; --小时     VM     NUMBER; --分     VS     NUMBER; --秒     RESULT VARCHAR2(100); --返回值 BEGIN     VD := TRUNC(DAYS);     VH := TRUNC((DAYS - VD) * 24);     VM := TRUNC((DAYS - VD - VH / 24) * 24 * 60);     VS := TRUNC((DAYS - VD - VH / 24 - VM / 24 / 60) * 24 * 60 * 60);     SELECT DECODE(VD, 0, '', VD || '天') || DECODE(VH, 0, '', VH || '小时') ||DECODE(VM, 0, '', VM || '分') || DECODE(VS, 0, '', VS ||'秒')   INTO RESULT FROM DUAL;     RETURN(RESULT); END;

TIMESTAMP 数据类型

它包括了所有DATE数据类型的年月日时分秒的信息，而且包括了小数秒的信息。

SELECT sysdate,systimestamp FROM dual

注意，to_char函数支持date和timestamp，但是trunc却不支持TIMESTAMP数据类型。

SELECT sysdate,systimestamp,to_char(systimestamp,'yyyymmdd hh24:mi:ssxff3') FROM dual

当你直接相减的话，看看会发生什么。结果将更容易理解，

select systimestamp-systimestamp from dual

这就意味着不用再通过麻烦的计算求出多少天多少秒，用substr函数摘取出其中的数字即可！

结论：当使用date和timestamp类型的时候，选择是很清楚的。你可以随意处置date和timestamp类型。当你的时间粒度必须精确至秒以下时，可以考虑使用timestamp。

再来看如何实现：将天数转换成“天时分秒”格式：

使用DATE数据类型：

create table t2 (d1 date,d2 date) ;INSERT INTO T2 VALUES (SYSDATE, SYSDATE + 1.1234);SELECT D1, D2, D2 - D1, F_DAYS2STR(D2 - D1),             CAST(D2 AS TIMESTAMP) - CAST(D1 AS TIMESTAMP)     FROM T2

SELECT TO_NUMBER(SUBSTR(D21, 1, INSTR(D21, ' '))) D,             SUBSTR(D21, INSTR(D21, ' ') + 1, 2) H,             SUBSTR(D21, INSTR(D21, ' ') + 4, 2) M,             SUBSTR(D21, INSTR(D21, ' ') + 7, 2) S     FROM (SELECT CAST(D2 AS TIMESTAMP) - CAST(D1 AS TIMESTAMP) D21            FROM T2)