在ORACLE中存储DATE和时间信息的话，实际上你有两种字段数据类型的选择（9i）。
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 DEFAULT0)
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-systimestampfrom dual
这就意味着不用再通过麻烦的计算求出多少天多少秒，用substr函数摘取出其中的数字即可！
结论：当使用date和timestamp类型的时候，选择是很清楚的。你可以随意处置date和timestamp类型。当你的时间粒度必须精确至秒以下时，可以考虑使用timestamp。
再来看如何实现：将天数转换成“天时分秒”格式：
使用DATE数据类型：
createtable 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(D1AS 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)
两个时间相减默认的是天数
oracle 两个时间相减默认的是天数*24 为相差的小时数
oracle 两个时间相减默认的是天数*24*60 为相差的分钟数
oracle 两个时间相减默认的是天数*24*60*60 为相差的秒数
如下:
SQL>select sysdate from dual;
2008-2-20 14:32:35
SQL> select (sysdate-to_date('2008-02-17 23:00:00','yyyy-mm-dd hh24:mi:ss')) from dual;
SQL> select (sysdate-to_date('2008-02-17 23:00:00','yyyy-mm-dd hh24:mi:ss'))*24 from dual;
SQL> select (sysdate-to_date('2008-02-17 23:00:00','yyyy-mm-dd hh24:mi:ss'))*24*60*60 from dual;1、对于2个DATE类型直接相减，就可以得到天数的值，如：
select to_date('2006-05-11 12:30:01','YYYY-MM-DD HH24:MI:SS') -to_date('2006-05-11 12:30:00','YYYY-MM-DD HH24:MI:SS') T from dual;
结果：
T
-------------------
.000011574
这个得到的天数很容易换算成秒，如 0.000011574*24*60*60=1s。
2、在ORACLE里日期类型是可以直接进行比较的。举个例子SQL> SELECT to_date('2006-12-05', 'yyyy-mm-dd') - to_date('2006-12-07', 'yyyy-mm-dd') FROM dual ;
这说明2006-12-05要比7号早2天。
SQL> SELECT to_date('2006-12-07 14:23:24' , 'yyyy-mm-dd hh24:mi:ss') + 1/24 FROM dual ;
这是说明在某一时间上加一小时，1是代表一天，1/24就是一小时，同理1/24/60就是一分钟
select trunc(sysdate) - trunc(to_date('2006-05-28 10:20','YYYY-MM-DD HH24:MI')) from dual3、datediff在Oralce中的实现：
create or replace function
-- -- works in roughly the same way as sybase datsdiff
-- call would be eg. datediff('month',date1,date2)
-- p_what would be 'HOUR', 'DAY','MONTH' OR 'QUARTER'
datediff( p_what in varchar2,
p_d1 in date,
p_d2 in date ) return number
as
l_result number;
begin
l_result:=;
if (upper(p_what) = 'HOUR') then
l_result:=((p_d2-p_d1)*24);
end if;
if (upper(p_what) = 'DAY') then
l_result:=(p_d2-p_d1);
end if;
if (upper(p_what) = 'MONTH') then
l_result:=round(MONTHS_BETWEEN(p_d2,p_d1),0);
end if;
if (upper(p_what) = 'QUARTER') then
l_result:=((floor(MONTHS_BETWEEN(p_d2,TRUNC(p_d2,'YEAR'))/3)+1) - (floor(MONTHS_BETWEEN(p_d1,TRUNC(p_d1,'YEAR'))/3)+1) + (((to_char(p_d2, 'yyyy')) - (to_char(p_d1, 'yyyy')))*4));
end if;
l_result:=floor(l_result);
return l_result;
end;
/
SQL> SELECT DATEDIFF('MONTH','11-JUL-1975','07-JUN-2004') FROM DUAL;
DATEDIFF('MONTH','11-JUL-1975','07-JUN-2004')
---------------------------------------------
347本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
