Oracle 时间类型及函数处理
Oracle 时间类型及函数处理
一、date类型使用而date类型相减只得到天数
SQL> SELECT sysdate-(sysdate-1) FROM dual;
SYSDATE-(SYSDATE-1)
-------------------
1
做个试验：
create table t2 (d1 date,d2 date) ;--创建一个表
INSERT INTO T2 VALUES (SYSDATE, SYSDATE + 1.1234); --插入一条数据
SQL> SELECT D1, D2, D2 - D1, CAST(D2 AS TIMESTAMP) - CAST(D1 AS TIMESTAMP) FROM T2;
D1 D2 D2-D1 CAST(D2ASTIMESTAMP)-CAST(D1AST
----------- ----------- ---------- ---------------------------------------
2010-12-2 1 2010-12-3 1 1.12340277 +000000001 02:57:42.000000
date类型相减转换为天、时分秒
SQL> SELECT TO_NUMBER(SUBSTR(D21, 1, INSTR(D21, ' '))) D,
2 SUBSTR(D21, INSTR(D21, ' ') + 1, 2) H,
3 SUBSTR(D21, INSTR(D21, ' ') + 4, 2) M,
4 SUBSTR(D21, INSTR(D21, ' ') + 7, 2) S
5 FROM (SELECT CAST(D2 AS TIMESTAMP) - CAST(D1 AS TIMESTAMP) D21
6 FROM T2) ;
D H M S
---------- ---- ---- ----
1 02 57 42
--两个date日期相减
select dt2 - dt1,
months_between(dt2, dt1) months_btwn,
numtodsinterval(dt2 - dt1, 'day') days,
numtoyminterval(months_between(dt2, dt1), 'month') years_months
from (select to_date('29-2-2000 01:02:03',
'dd-mm-yyyy hh24:mi:ss') dt1,
to_date('15-3-2001 11:22:33',
'dd-mm-yyyy hh24:mi:ss') dt2
from dual);
--转换为年，天时分秒
select numtoyminterval(months_between(dt2, dt1), 'month') years_months,
numtodsinterval(dt2 -
add_months(dt1, trunc(months_between(dt2, dt1))),
'day') days_hours
from (select to_date('29-2-2000 01:02:03', 'dd-mm-yyyy hh24:mi:ss') dt1,
to_date('15-3-2001 11:22:33', 'dd-mm-yyyy hh24:mi:ss') dt2
from dual);
二、timestamp类型
timestamp 语法：timestamp（n）
--当n=0时，与date长度一样
create table t (dt date,ts timestamp(0));
insert into t values (sysdate,systimestamp);
SQL> select dump(dt,10) dump,dump(ts,10) dump from t;
DUMP DUMP
------------------------------------------ ---------------------------------------
Typ=12 Len=7: 120,110,12,4,14,10,10 Typ=180 Len=7: 120,110,12,4,14,10,10
--n为9
create table t (dt date,ts timestamp(9));
insert into t values (sysdate,systimestamp);
SQL> select dump(dt,10) dump,dump(ts,10) dump from t;
DUMP DUMP
----------------------------------- --------------------------------------------------------
Typ=12 Len=7: 120,110,12,4,14,7,41 Typ=180 Len=11: 120,110,12,4,14,7,41,11,37,100,192
SQL> select * from t;
DT TS
----------- -----------------------------------
2010-12-4 1 04-12月-10 01.54.09.625000000 下午 --n为9即秒后面保留的位数（625000000）
select dump(ts,16) dump from t;
Typ=180 Len=11: 78,6e,c,4,e,37,a,25,40,be,40
SQL> select to_number('2540be40','xxxxxxxx') from dual;
TO_NUMBER('2540BE40','XXXXXXXX'）--将后四个字节转换为十进制
------------------------------
625000000 --后面四个字节就是秒小数点后面的数字
--timestamp 类型包括了所有DATE数据类型的年月日时分秒的信息，而且包括了小数秒的信息
SQL> SELECT sysdate,systimestamp FROM dual;
SYSDATE SYSTIMESTAMP
----------- ---------------------------------------
2011-3-4 15 04-3月 -11 03.18.16.625000 下午 +08:00
timestamp直接进行加减时会隐式的转为date型
SQL> select systimestamp ts,systimestamp+1 dt from dual;
TS DT
-------------------------------------------- -----------
04-12月-10 10.40.00.265000 上午 +08:00 2010-12-5 1
解决隐式转换问题（仍保持timestamp类型）
SQL> select systimestamp ts,systimestamp+numtodsinterval(1,'day') dt from dual;
TS DT
-------------------------------------------- ----------------------------------------------
04-12月-10 10.42.46.515000 上午 +08:00 05-12月-10 10.42.46.515000000 上午 +08:00
两个timestamp相减
SQL> select dt2 - dt1
2 from (select to_timestamp('29-2-2000 01:02:03.122000',
3 'dd-mm-yyyy hh24:mi:ss.ff') dt1,
4 to_timestamp('15-3-2001 11:22:33.000000',
5 'dd-mm-yyyy hh24:mi:ss.ff') dt2
6 from dual);
DT2-DT1
---------------------------------------
+000000380 10:20:29.878000000
将其转换为具体的年、天时分秒
SQL> select numtoyminterval(months_between(dt2, dt1), 'month') years_months,
2 dt2 - add_months(dt1, trunc(months_between(dt2, dt1))) days_hours
3 from (select to_timestamp('29-2-2000 01:02:03.122000',
4 'dd-mm-yyyy hh24:mi:ss.ff') dt1,
5 to_timestamp('15-3-2001 11:22:33.000000',
6 'dd-mm-yyyy hh24:mi:ss.ff') dt2
7 from dual);
YEARS_MONTHS DAYS_HOURS
--------------------------------------- ---------------------------------------
+000000001-00 +000000015 10:20:30.000000000
注：to_char函数支持date和timestamp，但是trunc却不支持TIMESTAMP数据类型
SQL> select trunc(to_date('20100908','yyyymmdd') - to_date('20100907','yyyymmdd')) m from dual;
M
----------
1
--两个相减的timestamp类型不能用trunc
SQL> select trunc(to_timestamp('20100908','yyyymmdd') - to_timestamp('20100907','yyyymmdd')) m from dual;
select trunc(to_timestamp('20100908','yyyymmdd') - to_timestamp('20100907','yyyymmdd')) m from dual
ORA-00932: 数据类型不一致: 应为 NUMBER, 但却获得 INTERVAL DAY TO SECOND
to_char的使用：
SQL> SELECT sysdate,systimestamp,to_char(systimestamp,'yyyymmdd hh24:mi:ssxff3') FROM dual;
SYSDATE SYSTIMESTAMP TO_CHAR(SYSTIMESTAMP,'YYYYMMDD'
----------- -------------------------------------------------------- ------------------------------
2010-12-2 1 02-12月-10 02.06.34.046000 下午 +08:00 20101202 14:06:34.046
对于 timestamp类型的数据直接相减可以精确到秒以后
SQL> select systimestamp-systimestamp from dual;
SYSTIMESTAMP-SYSTIMESTAMP
---------------------------------------
+000000000 00:00:00.000000
三、interval year to month 类型的使用
interval year to month 可以用numtoyminterval或to_yminterval函数替代
SQL> select numtoyminterval(5,'year') + numtoyminterval(2,'month') from dual;
NUMTOYMINTERVAL(5,'YEAR')+NUMT
---------------------------------------
+000000005-02
SQL> select numtoyminterval(5*12+2,'month') from dual;
NUMTOYMINTERVAL(5*12+2,'MONTH'
---------------------------------------
+000000005-02
SQL> select to_yminterval('5-2') from dual;
TO_YMINTERVAL('5-2')
---------------------------------------
+000000005-02
SQL> select interval '5-2' year to month from dual;
INTERVAL'5-2'YEARTOMONTH
---------------------------------------
+05-02
四、interval day to second 类型的使用
interval day to second 可以用numtodsinterval或to_dsinterval替换使用
SQL> select numtodsinterval(10, 'day') + numtodsinterval(2, 'hour') +
2 numtodsinterval(3, 'minute') + numtodsinterval(2.3312, 'second')
3 from dual;
NUMTODSINTERVAL(10,'DAY')+NUMT
---------------------------------------
+000000010 02:03:02.331200000
SQL> select numtodsinterval(10*24*60*60+2*3600+3*60+2.3312, 'second') from dual;
NUMTODSINTERVAL(10*24*60*60+2*
---------------------------------------
+000000010 02:03:02.331200000
SQL> select to_dsinterval('10 02:03:02.3312') from dual;
TO_DSINTERVAL('1002:03:02.3312'
---------------------------------------
+000000010 02:03:02.331200000
SQL> select interval '10 02:03:02.3312' day to second from dual;
INTERVAL'1002:03:02.3312'DAYTO
---------------------------------------
+10 02:03:02.331200
五、时间函数
ADD_MONTHS 函数
ADD_MONTHS函数将一个日期上加上一指定的月份数，所以，日期中的日将是不变的。然而，如果开始日期是某月的最后一天，那么，结果将会调整以使
返回值仍对应新的一月的最后一天。如果，结果月份的天数比开始月份的天数少，那么，也会向回调整以适应有效日期
select ADD_MONTHS(TO_DATE('15-11-1961','dd-mm-yyyy'),1) from dual; --1961-12-15
select ADD_MONTHS(TO_DATE('30-11-1961','dd-mm-yyyy'),1) from dual; --1961-12-31
select ADD_MONTHS(TO_DATE('31-1-1999','dd-mm-yyyy'),1) from dual;--1999-2-28
MONTHS_BETWEEN 函数
MONTHS_BETWEEN (x, y)用于计算x和y之间有几个月。如果x在日历中比y早，那么MONTHS_BETWEEN()就返回一个负数。
当x 和 y 之间的月份之差不是整月的时候，可以采用小数表示
NUMTODSINTERVAL()函数
NUMTODSINTERVAL(x, interval_unit)用于将以interval_unit指定的值为单位的数字x转换为一个INTERVAL DAY TO SECOND类型，
interval_unit参数可以设置为DAY、 HOUR、MINUTE或SECOND。
NUMTOYMINTERVAL()函数
NUMTOYMINTERVAL(x, interval_unit)用于将以interval_unit指定的值为单位的数字x转换为一个INTERVAL YEAR TO MONTH类型，
interval_unit参数可以设置为YEAR或MONTH。
评论暂时关闭